using vlookup to loop for a range of cells

[vranjit138]

hi friends

I have Invoice Numbers starting from range A1 to A4450 and in range B1 onwards i have Vendor Names . in the Vendor Report which begins in Range D6:d450, i have the invoice numbers and i require the vendor names there.. and i have to get the result for invoices in range D6:D450
in column E. how can i get the vlookup values by click of a button . the control button is taken from control toolbox

Please help

Regards
Ranjit

 

[ravishankar]

Hi
Paste the following codes in the macro window ( Alt F11)

Sub vendor()
For a = 6 To 450
Cells(a, 5) = Application.WorksheetFunction.VLookup(Cells(a, 4), Range("A1:B4450"), 2, False)
Next a
End Sub

Run the macro. It will lookup invoice numbers of colD in col A and returns matching vendor name in col E
Ravi

 

[jindon]

Why loop?

Private Sub CommandButton1_Click()
With Range("e6:e450")
     .Formula = "=vlookup(d6,$a$1:$b$4450,2,false)"
     .Value = .Value
End With
End Sub