Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help in VLOOKUP (second attempt)
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. I will try to make it more clear by following: I have already a UserForm that copies two variables from Sheet1 to position of A2 and B2 in Sheet2. In Sheet2, in cell 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. But it doesn't! I tried to use INDIRECT, but seems like it is not what I need for my needs. CLEAN and TRIM commands were used in VBA for (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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help in VLOOKUP (second attempt)
Here is the complete answer(s).
I think you main problem is how to use a name rane in VBA. Try this Range("TOTATL_POPULATION") Total population is a NAME in the workshet 1) If you are using VBa it is more efficient to use FIND the using a worksheet function. Here is how to do it in VBA efficiently with sheets("Sheet2") set c = Range("TOTATL_POPULATION").find(what:=.Range("A2") , _ lookin:=xlvalues,lookat:=xlwhole) if not c is nothing then ReturnValue = c.offset(0,1) else msgbox("did not find : " & .Range("A2")) end if end with 2) Using VLOOKUP() VLOOKUP need as string just like it would appear in the worksheet ReturnValue = worksheetfunction.vlookup("A2, B2, 2, FALSE") or lookupvalue = Range("A2") ReturnValue = worksheetfunction.vlookup(lookupvalue, _ Range("TOTATL_POPULATION") , 2, FALSE) or lookupvalue = Range("A2") ReturnValue = worksheetfunction.vlookup(lookupvalue & _ ", B2 , 2, FALSE") 3) The formula solution Range("Z100").formula = "=vlookup(A2, B2, 2, FALSE)" or lookupvalue = Range("A2") Range("Z100").formula = "=vlookup(" & lookupvalue & ", B2, 2, FALSE)" Notice the formula is a string lookupvalue = Range("A2") formulastring = "=vlookup(" & lookupvalue & ", B2, 2, FALSE)" Range("Z100").formula = formulastring 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. I will try to make it more clear by following: I have already a UserForm that copies two variables from Sheet1 to position of A2 and B2 in Sheet2. In Sheet2, in cell 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. But it doesn't! I tried to use INDIRECT, but seems like it is not what I need for my needs. CLEAN and TRIM commands were used in VBA for (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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help in VLOOKUP (second attempt)
Good morning Joel,
Thanks a lot for prompt responce, but somehow I am not able to figure out how to use your suggestions, if you don't mind, can you give a bit more of detail? I appreciate it very much. Dan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help in VLOOKUP (second attempt)
I don't like using worksheet function in VBA unless it makes sense. I prefer
the VBA find instead of VLOOK. This solution will work with any table on any sheet in the workbook. You need to extract the sheet name from the "Named Range" to prevent errors. Sub test() TableName = TOTAL_POPULATION 'you need to get the sheetname from the name range sheetname = ActiveWorkbook.Names(TableName ).RefersTo 'remove equal sign sheetname = Mid(sheetname, 2) 'extract sheetname from cell address sheetname = Left(sheetname, InStr(sheetname, "!") - 1) MyYear = 2000 CityState = "Cherokee, KS" With Sheets(sheetname).Range(TableName ) 'get year column in row 1 of table Set YearCell = .Rows(1).Find(what:=MyYear, _ LookIn:=xlValues, lookat:=xlWhole) If YearCell Is Nothing Then MsgBox ("Did not find Year : " & MyYear) Else 'get city row from column 1 of table Set CityCell = .Columns(1).Find(what:=CityState, _ LookIn:=xlValues, lookat:=xlWhole) If CityCell Is Nothing Then MsgBox ("Did not find City : " & CityState) Else MsgBox ("Data for Year and city = " & _ Sheets(sheetname).Cells(CityCell.Row, YearCell.Column)) End If End If End With "Dan" wrote: Good morning Joel, Thanks a lot for prompt responce, but somehow I am not able to figure out how to use your suggestions, if you don't mind, can you give a bit more of detail? I appreciate it very much. Dan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help in VLOOKUP (second attempt)
Hello Joel,
What you have developed is fantastic, but I am afraid I didn't explain well enough to make it meet my needs. The reason why I used UserForm/TextBox - TextBox1 copies variable such under column "County/State" from Sheet3 and pastes it into A1 in Sheet2, and TextBox2 copies variable from list of about 12 Table Names, such as for instance: Table Names from Sheet3/And in UserForm/Texbox2 County/State -variable from Sheet3, Column 1 1. Total_population Adair, MO 2. Property_value Boone, MO 3. Retail_sales Barry, MO 4. Business_establishment Bates, KS 5. Charges_public_revenues Bollinger, MO 6. Intergovernmental_transfer Bollinger, MO 7. Other_public_revenue Crawford, MO 8. Public_utility_revenue Gasconade, KS 9. Education_expenditure Dunklin, MO 10. Library_expenditure_forecast Lake, TN This is how Sheet3 should look like: it is just a portion, the table should go from Year €ś2000€ť to €ś2020€ť. From here I need to have chart to read data and create chart. A B C D E F G H 1 Total_population Adair, MO =VLOOKUP(B1,A1,2,FALSE) 24927 57298 57298 75820 56791 2 Property_value Boone, MO =VLOOKUP(B2,A2,2,FALSE) 1476 58798 256167 14517 75029 There are about 30 Table Names, I didnt list all of them €“ and I use in Sheet1 ComboBox to display €śCounty/State€ť variables (there are about 165 of them!) and that is why they are to be dynamic. By running UserForm- there are two TextBoxes (1st) copies and pastes value for €śCounty/State -variable from Sheet3, Column 1€ť and (2nd) copies and pastes value for €śTable Names from Sheet3/And in UserForm/Texbox2€ť. And this is where the problem starts: I need to have (or needed) VLOOKUP to read value in Cell €śA1€ť as dynamic TABLE_ARRAY for VLOOKUP function, so I can use the formula to 2000-2015 values to selected from Sheet3 of corresponding €ś€śCounty/State€ť and €śTable Names€ť from Sheet3. The values are used to create a chart at the end (which I was able to accomplish somehow). There was a question of why not too select values in Sheet3 €“ the answer is there are WAY TOO MANY equations and formulas there €“ by creating chart, which also has to be dynamic, i.e. corresponding to selected variables and FREEZES the machine. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help in VLOOKUP (second attempt)
First I would add a header row to sheet 3 that contains the Year starting in
column C so you can reference the year. Each of the Table should have the city in the first column and the years in the first row. first lookup the year in the first row using the following formula =MATCH(2000,OFFSET(Total_population,0,0,1,COLUMNS( Total_population))) The lookup the city in the first column =MATCH("Barry, MO",OFFSET(Total_population,0,0,ROWS(Total_populat ion),1) Now use index to get the intersection of the Row and column =Index(Total_population,Row#,Col#) the index if the combination of the two formulas above. =INDEX(Total_population,MATCH("Barry, MO",OFFSET(Total_population,0,0,ROWS(Total_populat ion),1)),MATCH(2000,OFFSET(Total_population,0,0,1, COLUMNS(Total_population)))) I hard coded the City Name and the Year to make it easier to understand. You can replace these with cell references. "Dan" wrote: Hello Joel, What you have developed is fantastic, but I am afraid I didn't explain well enough to make it meet my needs. The reason why I used UserForm/TextBox - TextBox1 copies variable such under column "County/State" from Sheet3 and pastes it into A1 in Sheet2, and TextBox2 copies variable from list of about 12 Table Names, such as for instance: Table Names from Sheet3/And in UserForm/Texbox2 County/State -variable from Sheet3, Column 1 1. Total_population Adair, MO 2. Property_value Boone, MO 3. Retail_sales Barry, MO 4. Business_establishment Bates, KS 5. Charges_public_revenues Bollinger, MO 6. Intergovernmental_transfer Bollinger, MO 7. Other_public_revenue Crawford, MO 8. Public_utility_revenue Gasconade, KS 9. Education_expenditure Dunklin, MO 10. Library_expenditure_forecast Lake, TN This is how Sheet3 should look like: it is just a portion, the table should go from Year €ś2000€ť to €ś2020€ť. From here I need to have chart to read data and create chart. A B C D E F G H 1 Total_population Adair, MO =VLOOKUP(B1,A1,2,FALSE) 24927 57298 57298 75820 56791 2 Property_value Boone, MO =VLOOKUP(B2,A2,2,FALSE) 1476 58798 256167 14517 75029 There are about 30 Table Names, I didnt list all of them €“ and I use in Sheet1 ComboBox to display €śCounty/State€ť variables (there are about 165 of them!) and that is why they are to be dynamic. By running UserForm- there are two TextBoxes (1st) copies and pastes value for €śCounty/State -variable from Sheet3, Column 1€ť and (2nd) copies and pastes value for €śTable Names from Sheet3/And in UserForm/Texbox2€ť. And this is where the problem starts: I need to have (or needed) VLOOKUP to read value in Cell €śA1€ť as dynamic TABLE_ARRAY for VLOOKUP function, so I can use the formula to 2000-2015 values to selected from Sheet3 of corresponding €ś€śCounty/State€ť and €śTable Names€ť from Sheet3. The values are used to create a chart at the end (which I was able to accomplish somehow). There was a question of why not too select values in Sheet3 €“ the answer is there are WAY TOO MANY equations and formulas there €“ by creating chart, which also has to be dynamic, i.e. corresponding to selected variables and FREEZES the machine. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help in VLOOKUP (second attempt)
Joel,
Fantastic job, but again, I am afraid I missed something again. May I ask you if I can just email the spreadsheet so you can have a look at this? I am afriad I have hard time explaining what I need to do. Dan. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help in VLOOKUP (second attempt)
I flying today and not sure when I will get to look at the spreadsheet. I
swa going to suggest that you e-mail me the spreadsheet. I should be able to look at it this afternoon. joel dot warburg at itt dot com "Dan" wrote: Joel, Fantastic job, but again, I am afraid I missed something again. May I ask you if I can just email the spreadsheet so you can have a look at this? I am afriad I have hard time explaining what I need to do. Dan. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need Help in VLOOKUP (second attempt)
Hello Joel,
This is Dan, just want to make sure if you have received my email. All the best. Dan. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct - Second Attempt | Excel Worksheet Functions | |||
Do until code attempt.. | Excel Programming | |||
my first attempt at R1C1 in vba | Excel Programming | |||
1st attempt vba-how do i whatnext in this sub | Excel Programming | |||
2nd attempt at excel VB commands | Excel Programming |