I'm stuck. Need help with a conditional statement.

Posted by Leon on August 31, 2001 5:21 AM

I am writing an IF Then macro that looks at each item within a name that I created (the name is a single column that contains numbers and strings). If the item is a number above a determined amount I copy it to another sheet, if it is a string I want to ignore the item and go to the next item. It's probably easy but I can't figure out how to write the condition that if the item is a string and not a number ignore it and look at the next item. Thanks to those that can help....

Posted by Ivan F Moala on August 31, 2001 6:31 AM

couple of diff ways to do this;

Sub test()
Dim ocell As Range
Dim CpyAddr As String
Dim c As Range

Dim st
st = Timer
For Each ocell In Range("myRg")
If Not IsNumeric(ocell) Then GoTo Skip
If ocell > 25 Then
With Sheets("sheet2")
Set c = .Cells(65536, 1).End(xlUp)
CpyAddr = c.Offset(1, 0).Address
ocell.Copy Destination:=.Range(CpyAddr)
End With
End If
Skip: Next
[b1] = Timer - st
End Sub

Sub test2()
Dim rg As Range
Dim ocell As Range
Dim c
Dim CpyAddr As String
Dim st

st = Timer

Set rg = Columns("A:A").SpecialCells(xlCellTypeConstants, 1)

For Each ocell In rg
If ocell > 25 Then
With Sheets("sheet2")
Set c = .Cells(65536, 1).End(xlUp)
CpyAddr = c.Address
ocell.Copy Destination:=.Range(CpyAddr).Offset(1, 0)
Set c = Nothing
End With
End If
Next
[c1] = Timer - st
End Sub

Not to much diff in speed.