Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF and THEN statements | Excel Discussion (Misc queries) | |||
the If Statements | Excel Worksheet Functions | |||
IF Statements (Mutliple Statements) | Excel Worksheet Functions | |||
IF Statements...I think | Excel Worksheet Functions | |||
operator statements, shorting when reusing one of the statements? | Excel Programming |