xluser2007

02-07-2009, 03:42 AM

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"):

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

and call this macro as follows:

Sub Pensioner1_Autofill()

Call Pensioner_Data_Autofill(Range("Section7_a"))

End Sub

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:

Sub Pensioner_Data_Autofill(rngSrcPensioner As Range)

rngSrcPensioner.Offset(2, 0).Formula = _

"=VLookup(Section7_a, SECTION7_Autofill_Data, 5, False)"

End Sub

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.

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"):

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

and call this macro as follows:

Sub Pensioner1_Autofill()

Call Pensioner_Data_Autofill(Range("Section7_a"))

End Sub

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:

Sub Pensioner_Data_Autofill(rngSrcPensioner As Range)

rngSrcPensioner.Offset(2, 0).Formula = _

"=VLookup(Section7_a, SECTION7_Autofill_Data, 5, False)"

End Sub

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.