Re: If Code
- From: "Rick Rothstein" <rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx>
- Date: Thu, 12 Nov 2009 13:20:21 -0500
Please read my comment to Jacob regarding the use of the Text property for this particular question. As for the construction of your code in general, I usually try to avoid using On Error traps when it is easy to code around them (as per the code I posted)... not that I have anything against On Error traps per se, it's just that I try to reserve their use for coding situations that require it or where its use simplifies the code's construction significantly.
--
Rick (MVP - Excel)
"John_John" <JohnJohn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:D275CB74-F75F-403E-A593-592E52D6E7E8@xxxxxxxxxxxxxxxx
One more way to skin the cat. :-)
Dim fIsValid As Boolean
On Error Resume Next
fIsValid = Not (CLng(CDate(Range("D4010"))) _
& --Range("F4010").Text _
& --Range("G4010").Text) _
Like "*[!0-9]*"
On Error GoTo 0
If fIsValid Then
ADD_TO_LIST
Else
MsgBox "Cell D4010 must be a date AND cells " _
& "F4010 & G4010 must be interger numbers!", vbExclamation
End If
Ο χρήστης "Rick Rothstein" έγγραψε:
Try your test using this code...
If Len(Range("F4010").Value) + Len(Range("G4010").Value) > 0 And _
Not Range("F4010").Value Like "*[!0-9]*" And _
Not Range("G4010").Value Like "*[!0-9]*" And _
IsDate(Range("D4010").Value) Then
The first test makes sure there is something in both F4010 and G4010; the
second line makes sure that whatever is in F4010 is made up of only digits;
the third line does the same thing for G4010; and the fourth line makes sure
that whatever is in D4010 is a date.
--
Rick (MVP - Excel)
"Neil Pearce" <Neil.Pearce@xxxxxxxxxxxx> wrote in message
news:1954AE57-A346-455B-861A-7ECFC34FC165@xxxxxxxxxxxxxxxx
>I have recorded the Sub "Add_to_List" as detailed below and attached it >to
>a
> button on my spreadheet.
>
> What lines would be required to add the condition that the macro will > only
> proceed:
>
> IF cell D4010 is a date AND cells F4010 & G4010 are interger numbers
> Carryout Macro ADD_TO_LIST
> ELSE provide a messagebox that states, "Cell D4010 must be a date AND
> cells
> F4010 & G4010 must be interger numbers"
>
> Many thanks
>
> Neil
>
>
>
> Sub ADD_TO_LIST()
> '
> ' ADD_TO_LIST Macro
> ' Macro recorded 12/11/2009 by Gleeds
> '
>
> '
> Range("D4010:H4010").Select
> Selection.Copy
> Range("D4008").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Range("D8:H4008").Select
> Range("H4008").Activate
> Application.CutCopyMode = False
> Selection.Sort Key1:=Range("D9"), Order1:=xlDescending,
> Key2:=Range("F9") _
> , Order2:=xlAscending, Key3:=Range("G9"), Order3:=xlAscending,
> Header:= _
> xlYes, OrderCustom:=1, MatchCase:=False,
> Orientation:=xlTopToBottom, _
> DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, > DataOption3:=
> _
> xlSortNormal
> Range("D4011:H4011").Select
> Selection.ClearContents
> ActiveWindow.ScrollRow = 9
> Range("A1").Select
>
>
> End Sub
.
.
- References:
- If Code
- From: Neil Pearce
- Re: If Code
- From: Rick Rothstein
- Re: If Code
- From: John_John
- If Code
- Prev by Date: RE: HELP with .HTMLBody: Display Hpyerlink in email body
- Next by Date: RE: Excel (or Access) Challenge
- Previous by thread: Re: If Code
- Next by thread: Long Block & "Name Box"
- Index(es):
Relevant Pages
|
