PDA

View Full Version : Loop through Vlookup



Offshoredude
12-20-2016, 09:41 AM
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
12-20-2016, 10:59 AM
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
12-20-2016, 12:03 PM
Hi,

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

YasserKhalil
12-20-2016, 01:24 PM
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
12-21-2016, 06:04 AM
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.