Results 1 to 5 of 5

Thread: Changing Multiple For Each Statements Together with Next - VBA

  1. #1

    Changing Multiple For Each Statements Together with Next - VBA



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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:
    
    
    Code:
    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!

  2. #2
    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

  3. #3
    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

  4. #4
    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

  5. #5
    Solved now. The solution was Set d = ws.Cells(c.row, 6). Thanks for all of your help!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •