Dynamic VBA vlookup

[Justin8506]

Hi There

How do I create a dynamic vlookup vba. Got stuck at creating a dynamic vlookup vba code. Below was the initial code.

Sub Vlookup()

Range("K2") = "=VLOOKUP(RC[-5], ('C:\Users\Desktop\Aging report\Mar22\[Book1.xlsx]Sheet3'!R2C5:R100C9),5,FALSE)"


End Sub

 

[Joe4]

Welcome to the Board!

How do you want it dynamic?
What should change and what does that depend on?

A few other issues:

1. NEVER, EVER use reserve words (names of existing Excel or VBA functions, properties, methods, objects etc) like "VLOOKUP" as the name of your procedures, functions, or variables. This could lead to errors or unexpected behaviors, as when you refer to "VLOOKUP", Excel cannot be sure if you mean the built-in function or your procedure.

2. It looks like your code is trying to set a formula using R1C1 referencing.
As such, it needs to start:

Range("K2").FormulaR1C1 = ...

 

[Justin8506]

Welcome to the Board!

How do you want it dynamic?
What should change and what does that depend on?

A few other issues:

1. NEVER, EVER use reserve words (names of existing Excel or VBA functions, properties, methods, objects etc) like "VLOOKUP" as the name of your procedures, functions, or variables. This could lead to errors or unexpected behaviors, as when you refer to "VLOOKUP", Excel cannot be sure if you mean the built-in function or your procedure.

2. It looks like your code is trying to set a formula using R1C1 referencing.
As such, it needs to start:

Range("K2").FormulaR1C1 = ...

thanks for the response Joe4. I managed to figure out the dynamic vlookup code after multiple trial and error

 

[Joe4]

thanks for the response Joe4. I managed to figure out the dynamic vlookup code after multiple trial and error

Glad you figured it out.
Bear in mind what I told you about naming your procedures, as what you had originally can cause yourself a lot of headaches.

 

[Justin8506]

Glad you figured it out.
Bear in mind what I told you about naming your procedures, as what you had originally can cause yourself a lot of headaches.

hi Joe4, would you be able to advise why am I getting a 400 error when I have the vba code running fine for sheet1 but not sheet3 (after changing the sheet name)?

 

[Joe4]

hi Joe4, would you be able to advise why am I getting a 400 error when I have the vba code running fine for sheet1 but not sheet3 (after changing the sheet name)?

Really hard to say without having access to everything, but have a look here: VBA Error 400: What Are the Causes and How to Fix It? - VBA and VB.Net Tutorials, Education and Programming Services