vlookup with multiselect listbox results

[OldManDemosthenes]

I have a userform with two listbox (one for cities, and one for census tracts) and two combobox (year, and 'census' or 'dof' data). All of the city names and tracts are located one one sheet ("InputData"), and I have the code for the userform to place the selected values on a second sheet ("AutoRun").

What I am having trouble figuring out is how to make the userform use the vlookup function to pull data about the listbox selection(s) from another sheet. For example, if I select "Seattle" in the city listbox, the year is "2009" and "Census" for the combobox, I would like to use a vlookup to pull data from the "CensusCityData09" worksheet range(D:G), column 4. If a census tract is selected the data is pulled from the "CensusTractData09" worksheet.

The vlookup data for cities needs to go on "AutoRun" column E in the row that corresponds with the city value, and column j for Census tract. There is also a header row. Is there a way to place a sum function in this row to sum all of the vlookup values?

Here's my code to pull just the listbox selections:
Thanks!

Private Sub cbRun_Click()
[INDENT]Dim i As Long, j As Long, cty As String, trct As String[/INDENT]
[INDENT]With lbCity[/INDENT]
[INDENT][INDENT]For i = 0 To .ListCount - 1[/INDENT][/INDENT]
[INDENT][INDENT][INDENT]If .Selected(i) Then[/INDENT][/INDENT][/INDENT
[INDENT][INDENT][INDENT][INDENT]cty = cty & .List(i, 1) & ", "[/INDENT][/INDENT][/INDENT][/INDENT]
[INDENT][INDENT][INDENT]End If[/INDENT][/INDENT][/INDENT]
[INDENT][INDENT]Next i[/INDENT][/INDENT]
[INDENT]End With[/INDENT]
[INDENT]With lbTract[/INDENT]
 [INDENT][INDENT]For j = 0 To .ListCount - 1[/INDENT][/INDENT]
[INDENT][INDENT][INDENT]If .Selected(j) Then[/INDENT][/INDENT][/INDENT]
 [INDENT][INDENT][INDENT][INDENT]trct = trct & .List(j, 1) & ", "[/INDENT][/INDENT][/INDENT][/INDENT]
[INDENT][INDENT][INDENT]End If[/INDENT][/INDENT][/INDENT]
[INDENT][INDENT]Next j[/INDENT][/INDENT]
[INDENT]End With[/INDENT]
    
[INDENT]Worksheets("AutoRun").Cells(8, 1) = cbYear.Value[/INDENT]
[INDENT]If obCensus.Value = True Then Worksheets("AutoRun").Cells(10, 1) = "Census" Else[/INDENT]
[INDENT]If obDOF.Value = True Then Worksheets("AutoRun").Cells(10, 1) = "DOF"[/INDENT]
[INDENT]Range("D:M").ClearContents[/INDENT]
[INDENT]With lbCity[/INDENT]
[INDENT][INDENT]For i = 0 To .ListCount - 1[/INDENT][/INDENT]
[INDENT][INDENT][INDENT]If .Selected(i) Then[/INDENT][/INDENT][/INDENT]
[INDENT][INDENT][INDENT][INDENT]Cells(Rows.Count, "D").End(xlUp)(2) = .List(i)[/INDENT][/INDENT][/INDENT][/INDENT]
[INDENT][INDENT][INDENT]End If[/INDENT][/INDENT][/INDENT]
[INDENT]Next i[/INDENT]
[INDENT]End With[/INDENT]
[INDENT]With lbTract[/INDENT]
[INDENT][INDENT]For j = 0 To .ListCount - 1[/INDENT][/INDENT]
[INDENT][INDENT][INDENT]If .Selected(j) Then[/INDENT][/INDENT][/INDENT]
[INDENT][INDENT][INDENT][INDENT]Cells(Rows.Count, "I").End(xlUp)(2) = .List(j)[/INDENT][/INDENT][/INDENT][/INDENT]
[INDENT][INDENT][INDENT]End If[/INDENT][/INDENT][/INDENT]
[INDENT][INDENT]Next j[/INDENT][/INDENT]
[INDENT]End With[/INDENT]
[INDENT]Unload Me[/INDENT]
End Sub

 

[Norie]

Something has happened to the code but I think I might know what it is.

Anyway, you might be able to use VLOOKUP for this but since this is in VBA why not use the Find method?

It's hard to tell which to use without knowing more about the data and how it's organised.

What's in the relevant columns of the City and Tract worksheets?

Also how many of those worksheets do you have?

1 for each city for each year and 1 for each tract for each year?

Perhaps most importantly what will you actually be looking up/searching for?

 

[OldManDemosthenes]

Hi Norie. Thanks for responding once again - you've been a tremendous help with this project!

I have 4 worksheets per year (currently 2007 - 2009, although I will add 2010 when the data becomes available and so on). What I am doing is collecting population data from the US Census Bureau and another source for each year of the model. Hence, I have both a "CensusCityData(year)" and "DOFCityData(year)" worksheet and two more with Tract instead of City. These data sheets area identical in the way they are set up. All of the cities or tracts are listed starting in column D row 3. The total amount varies between city and tract and from year to year. Columns E, G, I, K all contain information that I need to list next to the areas selected from the userform which is why I was trying to figure out how to use a vlookup function in the userform.

In the end I want all the data listed on the "AutoRun" worksheet with name of the cities selected in column D and the name of the tracts selected in column I. The relevant data for the cities would then be listed next to it in columns E, F, G, H. In the header row I would like to sum the column. So if column E is population, row one would have the entire population for all of the selected cities. The same goes for tracts.

Hope that's enough information. I'm willing to try whatever you suggest. You can also give the code for how to do this once, and I will try to modify it to fit the rest of my requirements.

Thanks again for all of your help!