Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to insert VLOOKUP function in VBA?
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to insert VLOOKUP function in VBA?
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. --- Regards. Norman "Dan" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to insert VLOOKUP function in VBA?
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to insert VLOOKUP function in VBA?
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. --- Regards. Norman "Dan" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to insert VLOOKUP function in VBA?
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to insert VLOOKUP function in VBA?
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. --- Regards. Norman "Dan" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to insert VLOOKUP function in VBA?
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP insert rows | Excel Worksheet Functions | |||
customise Insert Function/Function Arguments dialog box | Excel Programming | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Insert function - custom function name preceded by module name | Excel Programming | |||
Insert value from vlookup and not formula with VBA | Excel Programming |