Using VBA in Excel, how do I loop through rows and set cell values?

Question

I'm trying to teach myself some VBA. I have a workbook with 2 worksheets, Sheet1 and Sheet2. I found this code below and modified it to work for me. Gets the value of Sheet1!B1, looks for it in Sheet2!K:K, and sets Sheet1!D1 to the value of a cell in column E (K - 6) of Sheet2.

How can I loop this so it looks at B1:B100 and sets D1:D100?

Sub Looping()
    Dim rng1 As Range
    Dim strSearch As String
    strSearch = Range("B1").Value
    Set rng1 = Worksheets("Sheet2").Range("K:K").Find(strSearch, , xlValues, xlWhole)
    If Not rng1 Is Nothing Then
        Range("D1").Value = rng1.Offset(0, -6)
    Else
        MsgBox strSearch & " not found"
    End If
End Sub

Thanks!

Small inconsistency between your text and your code: in the text you set D1 to a value from column K, in the code you set it to a value in column E (.Offset(0, -6). Please edit your question! — Peter Albert

Peter Albert Peter Albert · Accepted Answer · 2013-01-25T22:11:02

First of all, you don't need a VBA solution for that problem. Simply put this formula in Sheet1!D1:D100:

=IFERROR(INDEX(Sheet2!$E:E$,MATCH(B1,Sheet2!$K:$K,0))),"Not found")

In case you want to do this with VBA, here is your code in a loop:

Sub Looping()
    Dim rngTarget As Range
    Dim rngSearched as Range

    For Each rngTarget in Sheets("Sheet1").Range("D1:D100")
        Set rngSearched = Sheets("Sheet2").Range("K:K").Find( _
            rngTarget.Offset(, -2).Value, , xlValues, xlWhole)    
        If rngSearch Is Nothing Then
            rngTarget.Value = "not found"
        Else
            rngTarget.Value = rngSearch.Offset(, -6)
        End If
    Next rngTarget
End Sub

Alternatively, combine both approaches:

Sub FillDirectly
    With Sheets("Sheet1").Range("D1:D100")
        .Formula="=IFERROR(INDEX(Sheet2!$E:E$,MATCH(B1,Sheet2!$K:$K,0))),""Not found"")"
        .Calculate
        .Value = .Value 'Creates a value copy
    End With
End Sub

Using VBA in Excel, how do I loop through rows and set cell values?

1 Answers