Loop through Vlookup

[Offshoredude]

Gents,

I believe this is quite simple, but..... not to me :-)

How can I make the this code, keep checking for new entries from C10 to ?? and make it stop if it comes to an empty cell in "C"

Sub VlookUp()
Dim ws1 As Worksheet
Set ws1 = Sheets("2_COPY_MAST_LIST")
ws1.Range("O10").Value = WorksheetFunction.VlookUp(ws1.Range("N10").Value, ws1.Range("AA8:AB39"), 2, 0)
ws1.Range("O11").Value = WorksheetFunction.VlookUp(ws1.Range("N11").Value, ws1.Range("AA8:AB39"), 2, 0)
ws1.Range("O12").Value = WorksheetFunction.VlookUp(ws1.Range("N12").Value, ws1.Range("AA8:AB39"), 2, 0)
ws1.Range("O13").Value = WorksheetFunction.VlookUp(ws1.Range("N13").Value, ws1.Range("AA8:AB39"), 2, 0)
ws1.Range("O14").Value = WorksheetFunction.VlookUp(ws1.Range("N14").Value, ws1.Range("AA8:AB39"), 2, 0)
ws1.Range("O15").Value = WorksheetFunction.VlookUp(ws1.Range("N15").Value, ws1.Range("AA8:AB39"), 2, 0)
End Sub

[Bob Phillips]

Sub VlookUp()
Dim ws1 As Worksheet
Dim lastrow As Long
Dim i As Long

    Set ws1 = Sheets("2_COPY_MAST_LIST")

    With ws1
    
        lastrow = .Cells(Rows.Count, "C").End(xlUp).Row
        .Cells(i + 9, "O").Value = WorksheetFunction.VlookUp(.Cells(i + 9, "N").Value, .Range("AA8:AB39"), 2, 0)
    End With
    
    Set ws1 = Nothing
End Sub

[Offshoredude]

Hi,

Sorry mate, but it wont work, can you see why?

[YasserKhalil]

May be

Sub VlookUp()    Dim ws1 As Worksheet
    Dim lastrow As Long
    Dim i As Long


    Set ws1 = Sheets("2_COPY_MAST_LIST")


    With ws1
        lastrow = .Cells(Rows.Count, "C").End(xlUp).Row
        For i = 10 To lastrow
            .Cells(i, "O").Value = WorksheetFunction.VlookUp(.Cells(i, "N").Value, .Range("AA8:AB39"), 2, 0)
        Next i
    End With


    Set ws1 = Nothing
End Sub

[Aussiebear]

Hi,

Sorry mate, but it wont work, can you see why?

No, can you please explain why? There's no excuse for being lazy, post the reason why you think it doesn't work.