Help with for/next statements
 

I have this code that works

Sub NAMEINLAP1()
Dim t As Integer

With Worksheets("cardata1").Activate
ActiveSheet.Range("a1:h500").Select
For Each c In Selection.Columns(1).Range("a2:a20")
If Not IsNumeric(c) Then c.Offset(, 1) = "IN LAP"
Next
End Sub

However I want to loop it through 26 sheets in the format "cardata" & i
so i tried this;

Sub NAMEINLAP1()
Dim t As Integer
Dim i As Integer

For i = 1 To 26
With Worksheets("cardata" & i).Activate
ActiveSheet.Range("a1:h500").Select
For Each c In Selection.Columns(1).Range("a2:a20")
If Not IsNumeric(c) Then c.Offset(, 1) = "IN LAP"
Nextc
Next i

End Sub

However there is a problem with this. I guess it is to do with the
double for/next statements but I am not sure of an alternative.

Many thanks for your help.

Help with for/next statements
 

Sub NAMEINLAP1()
Dim t As Integer
Dim i As Integer

For i = 1 To 26
With Worksheets("cardata" & i)
For Each c In .Range("a1:h500").Columns(1).Range("a2:a20")
If Not IsNumeric(c) Then c.Offset(, 1) = "IN LAP"
Nextc
End With
Next i

End Sub


--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"keri" wrote in message
ps.com...
I have this code that works

Sub NAMEINLAP1()
Dim t As Integer

With Worksheets("cardata1").Activate
ActiveSheet.Range("a1:h500").Select
For Each c In Selection.Columns(1).Range("a2:a20")
If Not IsNumeric(c) Then c.Offset(, 1) = "IN LAP"
Next
End Sub

However I want to loop it through 26 sheets in the format "cardata" & i
so i tried this;

Sub NAMEINLAP1()
Dim t As Integer
Dim i As Integer

For i = 1 To 26
With Worksheets("cardata" & i).Activate
ActiveSheet.Range("a1:h500").Select
For Each c In Selection.Columns(1).Range("a2:a20")
If Not IsNumeric(c) Then c.Offset(, 1) = "IN LAP"
Nextc
Next i

End Sub

However there is a problem with this. I guess it is to do with the
double for/next statements but I am not sure of an alternative.

Many thanks for your help.

Help with for/next statements
 

Keri,

I think this is what you are looking for you. You appeared to have extra
selctions that you didn't need.

I've also deleted the with and moved it to the for each stmt.

Sub NAMEINLAP1()
Dim i As Integer
Dim c as Range

For i = 1 To 26
For Each c In Worksheets("cardata" & i).Range("a2:a20")
If Not IsNumeric(c) Then c.Offset(0, 1) = "IN LAP"
Next c
Next i
End Sub

--
Hope this helps
Martin Fishlock
Please do not forget to rate this reply.


"John Bundy" wrote:

I think your problem is the lack of an End With between your two nexts, that
and a space between next and c.

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"keri" wrote:

I have this code that works

Sub NAMEINLAP1()
Dim t As Integer

With Worksheets("cardata1").Activate
ActiveSheet.Range("a1:h500").Select
For Each c In Selection.Columns(1).Range("a2:a20")
If Not IsNumeric(c) Then c.Offset(, 1) = "IN LAP"
Next
End Sub

However I want to loop it through 26 sheets in the format "cardata" & i
so i tried this;

Sub NAMEINLAP1()
Dim t As Integer
Dim i As Integer

For i = 1 To 26
With Worksheets("cardata" & i).Activate
ActiveSheet.Range("a1:h500").Select
For Each c In Selection.Columns(1).Range("a2:a20")
If Not IsNumeric(c) Then c.Offset(, 1) = "IN LAP"
Nextc
Next i

End Sub

However there is a problem with this. I guess it is to do with the
double for/next statements but I am not sure of an alternative.

Many thanks for your help.