PDA

View Full Version : Changing Multiple For Each Statements Together with Next - VBA



blahbla
07-30-2012, 04:19 PM
Hello,

My question is how to make 2 variables change at the same time with a "for each" statement in VBA. For example, I would like to make the C and D variable change together instead of only one changing. My problem is that every time my code is doing a loop, only the C variable is changing. This is being done on 2 different columns where I want it to go to the next row each time such as
1 2
2 4
5 6



Sub stuff()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
ActiveSheet.Range(Cells(2, 2), Cells(65536, 2).End(xlUp)).Select
Selection.Copy
Range("C2").Select
ActiveSheet.Paste
Set ws = ActiveWorkbook.Sheets("CMRData")
Dim c As Range
Dim d As Range
For Each c In ActiveSheet.Range(Cells(2, 3), Cells(65536, 3).End(xlUp))
For Each d In ActiveSheet.Range(Cells(2, 6), Cells(65536, 6).End(xlUp))
If Not IsNumeric(d.Value) Then
If Len(c.Value) = 6 Then
c.Value = Left(c.Value, 5)
End If
ElseIf Not IsNumeric(d.Value) Then
If Len(c.Value) = 5 Then
c.Value = Left(c.Value, 5)
End If
Else
c.Value = ""
End If
Exit For
Next d
Next c
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Thanks

p45cal
07-30-2012, 11:37 PM
maybe something along these lines:?
lasrtrow = Cells(65536, 3).End(xlUp).Row
For rw = 2 To lastrow
If Not IsNumeric(Cells(rw, 6).Value) Then
If Len(Cells(rw, 3).Value) = 6 Then Cells(rw, 3).Value = Left(Cells(rw, 3).Value, 5)
Else
Cells(rw, 3).Value = ""
End If
Next rw

Bob Phillips
07-31-2012, 02:21 AM
Cross-posted at ExcelGuru http://www.excelguru.ca/forums/showthread.php?1008-Changing-Multiple-For-Each-Statements-Together-with-Next-VBA

GTO
07-31-2012, 03:48 AM
Greetings blahbla,

I see that you are a brand new member, so let me be the first to welcome you to vbaexpress :-)

Would you please read Here (http://www.excelguru.ca/content.php?184-A-message-to-forum-cross-posters)?

I cannot imagine putting it better tahn Ken Puls has already done, but imagine if you will, going to a town forum of olden times, and after you and maybe other townsfolk put effort into solving a fellow citizen's "issue", that the fellow had also asked for free help in other towns; all whilst you are in a hurry to keep up with your own fields.

I hope that makes sense, and again, welcome here. There's some mighty smart and helpful folks here. I am certain that you will be glad you joined:-)

Mark