Vlookup

[rubinda]

I am trying to lookup the value in column I, in worksheet "QA Hold", and paste the value from the lookup table in worksheet “Master Part Numbers” A2:B1000, column B, and paste in column J in worksheet “QA Hold”.

If I were doing a formula, it would look like this: =VLOOKUP(I2,'Master Part Numbers'!A:B,2,FALSE)

I want to do this without the formula, just code, and I would like to start pasting in J2 and loop.

I have the following code, but it returns “#NAME?”. Can you please assist? Hoping I am not too far off.

Sub VlookupPartDesc()
Dim lLastRow As Long

With Sheets("QA Hold")
lLastRow = .Cells(.Rows.Count, "I").End(xlUp).Row
With .Range("J2:J" & lLastRow)
.FormulaR1C1 = "=VLOOKUP(I2,'Master Part Numbers'!A2:B1000,2,FALSE)"
.Value = .Value
End With
End With

End Sub

 

[AlphaFrog]

Change this...
.FormulaR1C1 = "=VLOOKUP(I2,'Master Part Numbers'!A2:B1000,2,FALSE)"

To this...
.Formula = "=VLOOKUP(I2,'Master Part Numbers'!$A$2:$B$1000,2,FALSE)"

 

[sheetspread]

Just type .Formula without the R1C1. .Value will work too.

 

[rubinda]

Perfect. Thank you! How d get it to automatically enter the data in column J without having to run the macro?

 

[sheetspread]

Relative references entered to a range from VBA are increased just as they would be if you applied the fill or copy/paste.

 

[rubinda]

I don't know what that means. I am fairly new to this. Does that mean I need to do something different for the data to automatically populate?

 

[sheetspread]

I thought you were asking why the references changed automatically, but it sounds like you want an event change code to fill the vlookup formula without having to run the macro, correct?

 

[rubinda]

I thought you were asking why the references changed automatically, but it sounds like you want an event change code to fill the vlookup formula without having to run the macro, correct?

you are correct. Thank you.

 

[sheetspread]

Maybe:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = Cells(Rows.Count, "I").End(xlUp).Row and Target.Column = 9 then
Call VlookupPartDesc
End Sub