loop value excel vba

[luca90]

Please a fast method to loop value in column from other value in other column...

Admit in comun A (started from A2) have 98 filled cells.
Admit in comun H (started from H2) have 22 filled cells.

How to loop the first value of A2 in column A to all values in column H, How to to loop the first value of A3 in column A to all values in column H... ecc do until in column A the last cell is blank

Tks.

[Siddharth Rout]

Two Scenarios

1) If you know the start cell and end cell of Col A and Col H

for example A2:A98 and H2:H22 then use this code...

'~~> If start cell and last cell is known in Col A and Col H
Sub LoopCells()
    Dim i As Long, j As Long
    For i = 2 To 98 '<~~ for Col A
        For j = 2 To 22 '<~~ for Col H
            If Range("A" & i).Value = Range("H" & j) Then '<~~ or whatever comparison you want to make
                '~~> Your code
            End If
        Next j
    Next i
End Sub

2) If you know the start cell but not the end cell of Col A and Col H

for example A2:A?? and H2:H?? then use this code...

'~~> If start cell is known in Col A and Col H
'~~> But end cell is not known
Sub LoopCells()
    Dim rng1 As Range, rng2 As Range, aCl As Range, hCl As Range
    Dim strCol_A_addr As String, strCol_H_addr As String
    
    '~~> Get the Col A Range
    strCol_A_addr = Range(Range("A2"), Range("A2").End(xlDown)).Address
    Set rng1 = Range(strCol_A_addr)
    
    '~~> Get the Col H Range
    strCol_H_addr = Range(Range("H2"), Range("H2").End(xlDown)).Address
    Set rng2 = Range(strCol_H_addr)
    
    For Each aCl In rng1  '<~~ for Col A
        For Each hCl In rng2  '<~~ for Col H
            If aCl.Value = hCl.Value Then '<~~ or whatever comparison you want to make
                '~~> Your code
            End If
        Next
    Next
End Sub

Hope this helps...

[luca90]

hI Sid...
I use the second option... is really wath i want!
Tks.

[luca90]

Two Scenarios

1) If you know the start cell and end cell of Col A and Col H

for example A2:A98 and H2:H22 then use this code...

'~~> If start cell and last cell is known in Col A and Col H
Sub LoopCells()
    Dim i As Long, j As Long
    For i = 2 To 98 '<~~ for Col A
        For j = 2 To 22 '<~~ for Col H
            If Range("A" & i).Value = Range("H" & j) Then '<~~ or whatever comparison you want to make
                '~~> Your code
            End If
        Next j
    Next i
End Sub

2) If you know the start cell but not the end cell of Col A and Col H

for example A2:A?? and H2:H?? then use this code...

'~~> If start cell is known in Col A and Col H
'~~> But end cell is not known
Sub LoopCells()
    Dim rng1 As Range, rng2 As Range, aCl As Range, hCl As Range
    Dim strCol_A_addr As String, strCol_H_addr As String
    
    '~~> Get the Col A Range
    strCol_A_addr = Range(Range("A2"), Range("A2").End(xlDown)).Address
    Set rng1 = Range(strCol_A_addr)
    
    '~~> Get the Col H Range
    strCol_H_addr = Range(Range("H2"), Range("H2").End(xlDown)).Address
    Set rng2 = Range(strCol_H_addr)
    
    For Each aCl In rng1  '<~~ for Col A
        For Each hCl In rng2  '<~~ for Col H
            If aCl.Value = hCl.Value Then '<~~ or whatever comparison you want to make
                '~~> Your code
            End If
        Next
    Next
End Sub

Hope this helps...

hI Sid...
I use the second option... is really wath i want!
Tks.

[Siddharth Rout]

Well, if your query is solved then do remember to come back and mark this thread resolved