Need help for Vlookup Macro accross sheets

[OctopusPrime]

Hi Everyone,

I am new to VBA and I was wandering if it was possible to create a macro that would be able to do the following.

When I paste the value in the Column A, that would correspond to a city, of the first sheet "FindCoord" I would like to have automatically the geographic location latitude in column B and longitude in Column C corresponding.
I have a listing of latitude and longitude for each city of each states of the US.
I have one sheet for each state.
So I would like the macro to look in sheet Alaska doesn’t find the name, goes to Alabama etc. for each of the 51 states.
And this for each name of column A in sheet “FindCoord”.
I attached a sample file.

If you could help me, I would really appreciate.

Thanks in Advance

[AB33]

column A in sheet “FindCoord”.
IS BLANK
what happens if you have duplicate names?

[JBeaucaire]

1) You'll need to go through your tables on the other sheets and remove the trailing spaces on some of the entries. Welbornville on the Alabama sheet won't match, for instance, due the hidden spaces after it in A6775.

You can use the TRIM() function in an another column to create cleaned up versions of those citynames, then copy, and PASTE SPECIAL > VALUES back over the column A values.

2) On the FindCoord sheet, in column L list all the sheets you want to search in the order you want to search them. This is important since "Bakersfield" exists in 4 places in the US, this will only find the first one.

3) After listing all the sheet names you want searched in column L, press CTRL-F3 to open the Name Wizard and create a new dynamic named range:

Name: MySheets
RefersTo: =OFFSET(FindCoord!$L$1, , , COUNTA(FindCoord!$L:$L), )


4) Enter this array formula in FindCoord cell B1:

=VLOOKUP($A1, INDIRECT("'" & INDEX(MySheets, MATCH(1, COUNTIF(INDIRECT("'" & MySheets &"'!A:A"), $A1), 0)) & "'!A:D"), 3, 0)

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

If there is no entry in A1 yet, you will see #N/A. If you have entered a city name the lattitude should appear if it is found.

5) Copy the cell B1 over into C1, and edit the 3 to be a 4 and the longitude will appear.

6) Now copy B1 and C1 together and paste in the next couple hundred rows down. Do not go any further as these array formulas are memory intense and you don't want 1000s of unneeded ones yet.

[OctopusPrime]

Thanks a million JBeaucaire.

It's doing perfectly what I was hoping.

[JBeaucaire]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[OctopusPrime]

Hi Again,

Sorry, but would it be actually possible to improve the accuracy of the resheach to actually do the research base on the column A of FindCoord instead of going through all the sheets ? I Am able to trigger the State code from the reporting so was hoping to reduce the loading time by finding directly the right sheet.
I tried to change the formula you gave me but I get #N/A when I replace MySheets by the cell in front of the reference cell.
I posted an example in case I was not really clear :S

Thanks,

[JBeaucaire]

Regular formulas..

C1: =VLOOKUP($B1, INDIRECT("'" & $A1 & "'!A:C"), 3, 0)
D1: =VLOOKUP($B1, INDIRECT("'" & $A1 & "'!A:D"), 4, 0)


If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.