Need Help in VLOOKUP (second attempt)

[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.
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!

 

[Joel]

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

 

[Dan]

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

 

[Joel]

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]

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 didn’t 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.

 

[Joel]

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_population),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_population),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]

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.

 

[Joel]

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]

Hello Joel,
This is Dan, just want to make sure if you have received my email. All the
best.
Dan.