For Each Problem

[next]

Option Explicit
Sub Test()
Dim LTD_xls As Worksheet, Open_dif As Worksheet
Dim rLTD As Range, rOPN As Range

Set LTD_xls = Workbooks("LTD Open.xls").Worksheets("open")
Set Open_dif = Workbooks("open.dif").Worksheets("open")

For Each rLTD In LTD_xls.Range("A2:A")
If IsEmpty(rLTD) Then
Exit For
End If

For Each rOPN In Open_dif.Range("A2:A")
If IsEmpty(rOPN) Then
Exit For
End If
If (rOPN = rLTD) Then
rOPN.EntireRow.Delete shift:=xlUp
End If
Next rOPN
Next rLTD
End Sub


Basicaly i have 2 sheets, this code "supposably" skans each cell in first column in Workbooks("LTD Open.xls").Worksheets("open") and matches it against Workbooks("open.dif").Worksheets("open") first column.
If identical cell cannot be found then row should be removed.

My Problem:
I get an error as soon as i get to For Each rLTD In LTD_xls.Range("A2:A")
which is the first "For". It sais: "Method "Range" of object "_Worksheet" failed". What am i doing wrong?

BTW, how can i skip one step in a loop if a value is not a number?
If i use this code:

If Not IsNumeric(rLTD) Then
Next rLTD
End If
blah blah blah...
Next rLTD

i get an error: "Next Without For" <- or something like this. What's wrong here?

Thanks!

[tstav]

if you're interested for the whole column A, change the
For Each rLTD In LTD_xls.Range("A2:A")

to
For Each rLTD In LTD_xls.Range("A2:A65536")

[Bob Phillips]

That is because



Range("A2:A")


is not avalid range, it has a column, and a start row but no ending row.

For the other bit



If IsNumeric(rLTD) Then
blah blah blah...
End If
Next rLTD

[next]

Wow you are fast! Thanks!


If IsNumeric(rLTD) Then
blah blah blah...
End If
Next rLTD


Is there a way to work it in reverse? In other languages you can use "continue", so if it's not a number then

If Not IsNumeric(rLTD)
continue

[tstav]

Since you want the blah blah blah to run only if IsNumeric(rLTD), write the code as follows.


For rLTD = x To y
If IsNumeric(rLTD) Then
blah blah blah...
End If
Next rLTD

[Bob Phillips]

Is there a way to work it in reverse? In other languages you can use "continue", so if it's not a number then

If Not IsNumeric(rLTD)
continue


You don't need continue, you just embed the code after the IF



If Not IsNumeric(rLTD) Then
blah blah blah...
End If
Next rLTD

[tstav]

Is there a way to work it in reverse? In other languages you can use "continue",


If you mean whether there is a VBA equivalent to this

For i = 1 To 10
If Not IsNumeric(i) Then
Continue '<---
Else
blah blah blah
End If
Next i


then the answer is no. VBA, as far as I know, does not have an equivalent to 'Continue'. You write your code putting your blah part of it inside the if (the desired condition)...endif, i.e. you skip the "If Not IsNumeric" and use only the "If IsNumeric", like xld already said.