How to insert VLOOKUP function in VBA?

[Dan]

I have a question:
Here is what I want to happen. I am using UserForm/TextBoxes in transfer
Argument 1 and Argument 2 in a Row 2 Sheet 2. And then I need to use in
VLOOKUP fucntion, as Argument 1 should be as a LOOKUP_VALUE and Argument 2 as
TABLE_ARRAY. Both or arguments should be dynamic, i.e. the actual data is
tracked in other sheet in the same workbook. Is there a way to make it either
in Excel or VBA?
Dan.

 

[Norman Jones]

Hi Dan,

Your question is unclear to me.

If you are asking for help with the
VBA syntax, try:

Res = Application.WorksheetFunction.VLookup(...)

or

Res = Application.VLookup(...)

If, however, you are asking if itis posssible
to use a Userform's TextBox values to
provide the function's argument values, I
see no problem. You could use something
like::

'=============>>
Option Explicit

Private Sub Command_Button()
Dim sStr As String
Dim sAddress As String
Dim Res As Variant
Dim Rng As Range
Const iCol As Long = 2

With Me
sStr = .TextBox1.Value
sAddress = .TextBox.Value
End With

Set Rng = ActiveSheet.Range(sAddress)

Res = Application.VLookup(sStr, Rng, iCol, False)
MsgBox Res

End Sub
'<<=============

If my suggestions are not helpful, you
might consider posting some addtional
explanatory detail.

 

[Dan]

Hi Norman,
I am sorry, I guess I am complicating things more than they should be
What I need to do is the following: I have already a UserForm that copies
two variables from Sheet1 to position of A2 and B2 in Sheet2. In Sheet2, C3,
I need to place a VLOOKUP formula so it reads cell B2 as dynamic table_array.
Since the 1st UserForm every time executes CommandButton it copies different
value. For instance, it may come as "TOTAL_POPULATION" (this has to be an
table_array, pre-determined in Sheet3 and broke according to years) and the
second variable as "Cherokee, KS". The function has to find "Cherokee, KS" in
"TOTAL_POPULATION" table_array which is already in Sheet3 and return value
for 2000 year (that would be =VLOOKUP(A2, B2, 2, FALSE)). The reason I wanted
to use UserForm, by inserting formula, it copies it as and "B2" and not the
table_array. And there are about 20 table_arrays, so, that the 1st UserForm
is used, a user has an option of selecting any other than "TOTATL_POPULATION"
and so one. I hope that makes senss.

 

[Norman Jones]

Hi Dan,

Perhaps I totally misunderstand, but why
will the formula:

=VLOOKUP(A2, B2, 2, FALSE)

not meet your needs?

Each time the Userform's update button is
clicked, the values of the cells A2 and B2
will be updated and, consequently, the
result of your lookup formula will furnish the
updated result.

 

[Dan]

Hi Norman,
I wish I knew! That is why I am trying to overcome the problem. In UserForm1
I used even (Trim(TextBox2.Text)) to make sure there are no blanks in
between. But somehow, I keep getting the "#N/A" error. Meanwhile, if I type
"TOTAL_POPULATION_FORECAST" table_array, it works perfect! Any ideas?

 

[Norman Jones]

Hi Dan,

I would check the Lookup Table and verify that
the contained instance of:

TOTAL_POPULATION_FORECAST

has no hidden initial or trailing spaces.

Provided that the 'massaged' TextBox value is
identical to the Table value, I would not anticipate
any problem and I would expect the formula to
return the requiste values.

 

[Dan]

This is weird - I double-checked the values in other table_arrays, but still
the VLOOKUP funciton doesn't give me the requested values somehow. I tried
other table_arrays - no luck.
Anybody knows what might be the problem?