Macro to change a multiplier based on a zipcode

[lschott52]

I have cell A18 CITY STATE ZIPCODE

I Have cell G15 A formula G14*.06875

I would like to be able to have a macro that
that I could put a certain zipcode in to find
and then change the multiplier.

I am trying to attach workbook.
It's been a few years since I have used the forum but
has been very helpful.
Linda

[alansidman]

I don't think you need a macro for this. A simple table with zip codes and multipliers would suffice to make a vlookup function work.

On a new sheet put your table in the following format

v	A	B	C
1	Zip Code	Multiplier
2	80487	0.0685
3	60444	0.0523
4	60445	0.0522
5	91322	0.0875
6	56515	0.06852
7
8

Then in cell G15 put this formula

=G14*VLOOKUP(RIGHT(A18,5)*1,Sheet1!$A$2:$B$7,2,0)

[lschott52]

Thanks so much for your help and putting your idea to good use however:
is there anyway I can add something into the formula that if the zip code is
not on sheet 1 the value that is in G15 stays as is it will not change?

Thanking you in advance if you can suggest a solution.

Linda

[lschott52]

I don't think you need a macro for this. A simple table with zip codes and multipliers would suffice to make a vlookup function work.

On a new sheet put your table in the following format

v	A	B	C
1	Zip Code	Multiplier
2	80487	0.0685
3	60444	0.0523
4	60445	0.0522
5	91322	0.0875
6	56515	0.06852
7
8

Then in cell G15 put this formula

=G14*VLOOKUP(RIGHT(A18,5)*1,Sheet1!$A$2:$B$7,2,0)

Can you help another step please...If the zip code is not listed on Sheet1 could the formula have something in it so it would leave the cell as is?

[lschott52]

Can you help another step please...If the zip code is not listed on Sheet1 could the formula have something in it so it would leave the cell as is?

[alansidman]

You will need a macro in this situation. Try this untested one, assuming your vlookup table is as previously shown

Please Login or Register  to view this content.

[lschott52]

Thanks oh so much for your help.
Linda

[alansidman]

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