PDA

View Full Version : Solved: Vlookup loop



wibbers2000
05-15-2007, 03:00 AM
I would like some help on a vlookup loop please.

I can hard code the formula into a cell and drag it
(=VLOOKUP(Full!H1,Jobs!A1:B5000,2,FALSE))

, however, I want a codeless page and would rather it be in vba code.

I have attached a sample and the code is in the full page and column "I".

could some please assist in the code to make it a loop to all rows with data in.

Regards

Bob Phillips
05-15-2007, 03:08 AM
[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '

wibbers2000
05-15-2007, 03:13 AM
XLD

sorry, but I dont understand the answer?

Bob Phillips
05-15-2007, 03:21 AM
Sorry, I have a problem in posting sometimes.



Public Sub ProcessData()
Const TEST_COLUMN As String = &quot;A&quot; '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i, &quot;H&quot;) = &quot;&quot; Then
.Cells(i, &quot;I&quot;).Value = &quot;&quot;
Else
.Cells(i, &quot;I&quot;).Value = Application.VLookup(.Cells(i, &quot;H&quot;).Value, Range(&quot;'Jobs'!A1:B5000&quot;), 2, False)
End If
Next i

End With

End Sub

wibbers2000
05-15-2007, 03:27 AM
XLD.
you are a god...

Thank you