Changing Multiple For Each Statements Together with Next - VBA

blahbla

New member
Joined
Jul 31, 2012
Messages
4
Reaction score
0
Points
0
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
Code:
[/COLOR]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 a bunch!
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,940
Reaction score
0
Points
36
Excel Version(s)
O365
Is this what you mean

Code:
Sub stuff()    Dim c As Range
    Dim d As Range
    
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    With ActiveSheet
    
        .Range(.Cells(2, 2), .Cells(.Rows.Count, 2).End(xlUp)).Copy .Range("C2")
    
        Set ws = ActiveWorkbook.Sheets("CMRData")
        For Each c In .Range(.Cells(2, 3), .Cells(.Rows.Count, 3).End(xlUp))
            
            If Not IsNumeric(c.Offset(0, 3).Value) Then
                    
                If Len(c.Value) = 6 Or Len(c.Value) = 5 Then
                    c.Value = Left(c.Value, 5)
                End If
            Else
                c.Value = ""
            End If
        Next c
    End With
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub
 

blahbla

New member
Joined
Jul 31, 2012
Messages
4
Reaction score
0
Points
0
Thanks. I need to go down 1 c value for every d value that I down down so it would look like this. I made a typo and the "
For Each d In ActiveSheet.Range(Cells(2, 6), Cells(65536, 6).End(xlUp)) " should actually be
"
For Each d In ws.Range(Cells(2, 6), Cells(65536, 6).End(xlUp)) " since it is looking in another sheet.
c d
11 11 <---1
111 111 <----2
111 333 <---3
44 555 <----4
 

blahbla

New member
Joined
Jul 31, 2012
Messages
4
Reaction score
0
Points
0
I changed my code and it fixed some problems, but the d value is not showing a value when I do the debugger so the not isnumeric statements are all showing up as possitive even if the d.value is numeric.

Code:
Sub sdasdas()
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))
        Set d = ws.Range("G2", ws.Range("G65536").End(xlUp))
        If Not IsNumeric(d.Value) Then
            If Len(c.Value) = 6 Then
            c.Value = Left(c.Value, 5)
            ElseIf Len(c.Value) = 5 Then
                c.Value = Left(c.Value, 4)
            End If
        Else
            c.Value = ""
        End If
        Next c
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
 

blahbla

New member
Joined
Jul 31, 2012
Messages
4
Reaction score
0
Points
0
Solved now. The solution was Set d = ws.Cells(c.row, 6). Thanks for all of your help!!
 
Top