[Solved] I can’t get my VBA Excel Macro to stop at the end of the row

by


If I understand your question correctly: for each Cell in H2:H98, you’re looking for a match in A2:A98. It won’t necessarily be on the same row. If you find a match in Column A, you want to take the value from Column B and put it in Column I on the same row as the search value we just looked for. In this case, this code will work:

Option Explicit
Sub Test()

Dim ws          As Worksheet
Dim srcRng      As Range        '' Source range
Dim schRng      As Range        '' Search range
Dim c           As Range
Dim search      As Range

Set ws = ThisWorkbook.Sheets(1)
Set srcRng = ws.Range("H2:H98")
Set schRng = ws.Range("A2:A98")

For Each c In srcRng     '' Iterate through your source range
    Set search = schRng.Find(c.Value, LookIn:=xlValues, SearchDirection:=xlNext)     '' Find the value from column H in column A
    If Not search Is Nothing Then
        c.Offset(, 1).Copy search.Offset(, 1)   '' Get the value from column B, from the same row as the value it found in column A
                                                '' Then paste that value in column I, on the same row as the value we searched for from column H
    End If
Next c

GoTo statements are generally (generally, not always) very, very bad practice. Especially in this kind of situation. You don’t need them, it just makes your code convoluted.

6

solved I can’t get my VBA Excel Macro to stop at the end of the row