VLookup in VBA with ActiveCell Reference

[brackington]

Hello,

I am brand new to VBA and this is my first post here so please bear with me. I have been researching how to use a vlookup in VBA and have been unable to come up with a solution so I thought I would ask here. I need the formula to be inserted with reference to the active cell because the cell will change dependent on how the workbook is filled out. From what I have read I should be able to do this with this code:

Range(ActiveCell.Offset(1, 2), ActiveCell.Offset(1, 2).End(xlDown)).Offset(0, 2).Formula = "= vlookup("& ActiveCell.Offset(1, 3) & ",Products!$I$5:$I$40,1,0)"

I have been able to validate that the values before the = sign work, but the lookup gives me a Run-time error '1004': Application-defined or object-defined error everytime. What am I missing? Your help with this would be greatly appreciated! Thanks so much!

 

[RickXL]

Hi and welcome to the MrExcel Message Board,

A good way to start debugging problems like this is to print out the character string with the problem. For instance:

Debug.Print "= vlookup(" & ActiveCell.Offset(1, 3) & ",Products!$I$5:$I$40,1,0)"

I put the value xxx in the cell it would pick up and I got this displayed:

= vlookup(xxx,Products!$I$5:$I$40,1,0)

Now I suspect that you did not want a value like that in your formula. It remains unchanged as the formula is copied down and xxx will not mean anything in the spreadsheet. I guess "xxx" would not have been any improvement, either.

Guessing somewhat, I think this is what you might be looking for:

Range(ActiveCell.Offset(1, 2), ActiveCell.Offset(1, 2).End(xlDown)).Offset(0, 2).Formula = _
"= vlookup(" & ActiveCell.Offset(1, 3).Address(False, False) & ",Products!$I$5:$I$40,1,0)"

That produces this (my active cell was C1):

= vlookup(F2,Products!$I$5:$I$40,1,0)

If you don't have the "(False, False)" in place it gave me $F$2 in every row. The "Falses" switch off absolute addressing.

I could not re-create your error 1004 but I hope this helps.

 

[brackington]

Thank you so much RickXL! The correction worked perfectly!

 

[RickXL]

Hi, Good news!

Thanks for the feedback.

Regards