ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   If Code (https://www.excelbanter.com/excel-programming/436097-if-code.html)

Neil Pearce
If Code
 
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

Jacob Skaria
If Code
 
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
---------------
Jacob Skaria


"Neil Pearce" wrote:

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

Sam Wilson
If Code
 
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

"Neil Pearce" wrote:

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

Rick Rothstein
If Code
 
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" wrote in message
...
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


Rick Rothstein
If Code
 
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.

--
Rick (MVP - Excel)


"Sam Wilson" wrote in message
...
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

"Neil Pearce" wrote:

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


Rick Rothstein
If Code
 
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.

--
Rick (MVP - Excel)


"Jacob Skaria" wrote in message
...
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
---------------
Jacob Skaria


"Neil Pearce" wrote:

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


Neil Pearce
If Code
 
Many thanks folks.

"Rick Rothstein" wrote:

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" wrote in message
...
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

.

John_John
If Code
 
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" wrote in message
...
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

.

Rick Rothstein
If Code
 
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" wrote in message
...
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" wrote in message
...
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

.


All times are GMT +1. The time now is 01:27 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com