Calculating and copying data into new sheet

[scottwilsonx]

Sorry, can I ask you one last question.

I have entered a msgbox at the start of the procedure, at which poin
the user is asked to enter the name of the city they wish to look at
eg: New York.
I have got this working, and when I enter the correct city name in th
box it outputs only data relating to that city.

However I want it to full proof. Is there a way to allow the user t
pick from all available cities in the msgbox ?!

THanks a million.

Scott

 

[Mike Fogleman]

Scott, it seems they have left you with your last question unanswered, so I
will give it a stab. First scrap the msgbox. You will need a listbox and you
will need to populate the listbox with a unique list of available
cities.From there you will need to pass the city chosen to your code.
Create a listbox from the Control Toolbox menu and place it right of column
E. Now you need a unique list of cities from column E. The easiest way is to
select cloumn E and use Data/Filter/Advanced and copy the list to the sheet
some where way out of sight. the code would look like this:
Range("E:E").AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Range("CA5000"), Unique:=True

That will put a list of each city way off the visible sheet for the listbox.
Now tell the listbox where the list is:

Dim rwcnt
rwcnt = Range("CA5000").CurrentRegion.Rows.Count + 5000 - 1
Sheet1.ListBox1.ListFillRange = "CA5001:CA" & rwcnt

We add 5000 to the number of rows with cities because that is the row number
we pasted to. Then we subtract 1 so we do not include the header in the
list. Then we tell the ListFillRange to start 1 row below the header
(CA5001).
To use the value selected from the list we can use another variable to
substitute in Bernie's line

If myCell(1, 5).Value = "New York" Then

Dim city as String
city = Sheet1.ListBox1.Value
If myCell(1, 5).Value = city Then

You will have to assemble the code in the correct sequence as you need it
used.
Cheers...Mike F