Search for Matching Cell and Copy a Value

[chartzell]

I need what seems to be a fairly straightforward and simple macro, but I haven't written VBA in over a year and keep drawing blanks. Here's what I'm trying to do in pseudo-code.

lRow = 2

Do
  Search Sheet2.ColumnA for value matching Sheet1.ColumnA.Row(lRow)

  If matching value is found Then
    copy corresponding value in Sheet2.ColumnB to Sheet1.ColumnD.Row(lRow)

  lRow++

Loop Until Len(Cells(lRow, A)) = 0

Any snippets of code or links to appropriate documentation are appreciated. I'll be browsing documentation and seeing if I can figure this out on my own in the mean time.

 

[davesweep]

You could do this without VBA...

In Sheet1, column D

=INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0))

 

[NeonRedSharpie]

You shouldn't need to loop unless I'm missing something.

Sub matchCell()

    Dim startRow As Integer
    Dim endRow As Long
    
    Dim searchArea As Range


    Set searchArea = Sheets(2).Range("A:B")
    
    startRow = 2
    endRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
    
    With Range(Cells(startRow, 4), Cells(endRow, 4))
    
        .FormulaR1C1 = "=VLOOKUP(RC1," & Sheets(2).Name & "!C1:C2,2,0)"
        .Value = .Value
    
    End With
    


End Sub

 

[chartzell]

Perfect. Thank you.