convert, extract city using a list and lookup formula to vba

[gentledragon]

Hello,

I've managed to use a formula that extracts the city from an address cell, using an exhaustive city list. Formula is below. I tried using worksheetfunction. to write vba to do the same, but failed. Does anyone have any advice on how to use vba to do what the formula below is doing.

=LOOKUP(2^15,SEARCH(sheet2!$A$1:$A$765344,A1),sheet2!$A$1:$A$765344)

"a1" is the address cell i'm extracting from, sheet2!... is the city list array, the whole formula is on sheet3.

Thanks
Terry

[JBeaucaire]

If you have a working formula, why not just use it? Even in VBA, you can simply enter that formula into the range of cells needed, then remove the formula, leaving the results behind. This macro will enter that formula in column D down to the bottom of the data in column A, then remove the formulas:

Please Login or Register  to view this content.