If Code

N

Neil Pearce

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
 
J

Jacob Skaria

Hi Neil

Try the below which refers to the active sheet cell values

If IsDate(Range("D4010")) = True And _
IsNumeric(Range("F4010").Text) = True And _
IsNumeric(Range("G4010").Text) = True Then
'Call your procedure here
Else
MsgBox "Cell D4010 must be a date and F4010/G4010 must be integers"
Exit Sub
End If

If this post helps click Yes
 
S

Sam Wilson

testing if a number is an integer is easy:

if cint(myNumber) = myNumber then...

The difficulty is with checking for a date in a cell - dates are stored as
numbers - if you type 40129 into a cell and fromat it as a date you'll get
back to today.

Sam
 
R

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.
 
R

Rick Rothstein

if cint(myNumber) = myNumber then...

If the cell is empty and, hence, not an integer, your test will still
evaluate to True. It will also issue an error if the cell contains a value
that is not a number.
 
R

Rick Rothstein

Your tests have not assured the value in the cell is an integer value. Also,
I don't think I would use the Text property of the cell in your tests... if
the cell has a format, then, depending on what the format is doing, your
code could be fooled. For example, if a number is formatted to display with
some text after it (such as the appending of an apostrophe to indicate a
dimension of feet or a quote mark to indicate a dimension of inches), then
your IsNumeric test would fail.
 
N

Neil Pearce

Many thanks folks.

Rick Rothstein said:
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 said:
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
Click to expand...

.
Click to expand...
 
J

John_John

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 said:
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
Click to expand...

.
Click to expand...
 
R

Rick Rothstein

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 said:
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 said:
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
Click to expand...

.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Applying Variables to SORT 4
Sort error? 2
Macro Help 6
Suppressing a screen 2
Writing a Sort Macro 4
Macro Assist 2
Sort Errors 4
Sort by range 15

Top