-
Solved: VLOOKUP in VBA not working
HI All,
I have a worksheet userform which I am developing using VBA.
In this particular case, I would like when the user runs a macro to do a VLOOKUP in VBA and place the value in a specified cell in the macro.
Currently, If I run the following code to evaluate the VLOOKUP value in VBA and place it in the target range i.e. 2 rows below Range("Section7_a"):
[vba]Sub Pensioner_Data_Autofill(rngSrcPensioner As Range)
rngSrcPensioner.Offset(2, 0).Value = _
Application.Evaluate(Application.VLookup(rngSrcPensioner.Value, _
Range("SECTION7_Autofill_Data"), 5, False))
End Sub[/vba]
and call this macro as follows:
[vba]Sub Pensioner1_Autofill()
Call Pensioner_Data_Autofill(Range("Section7_a"))
End Sub[/vba]
It displays a #NAME? value in the cell 2 rows down from Range("Section7_a"), as specified.
However, as a means of debugging, I tried the following to display the full VLOOKUP Formula as follows:
[vba]Sub Pensioner_Data_Autofill(rngSrcPensioner As Range)
rngSrcPensioner.Offset(2, 0).Formula = _
"=VLookup(Section7_a, SECTION7_Autofill_Data, 5, False)"
End Sub[/vba]
It prints the full formula in the cell 2 rows below Range("Section7_a") as
"=VLOOKUP(Section7_a, SECTION7_Autofill_Data, 5, FALSE)" and prints the correct value as "M".
I don't understand why the first approach to evaluate the vlookup value (i.e. not display the formula) in VBA is not working? Could anyone please suggest how to make it work, as I woould not like to print a formula for the client?
As the form is confidential, it's difficult for me to post it online.
Any help appreciated.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules