Zip Code Pricing Macro

[bobnplano]

I am only a novice user & I would like to create a Macro that will automatically enter a price based on a zip code. I am using Excel 2002 on Windows XP Pro. I am a contractor with several subcontractors who work for me in different areas of the state of Texas. When I receive a statement from my company it only shows me the zip codes and the rate I am paid & I have to re-do the Excel spreadsheet & separate the subs by contractor numbers & then set the pricing by zip codes for the rate I pay those subs. I would like to take the statement I receive (in Excel format) & resort by Sub # & reset their rates.

EXAMPLE:

My Sub
Rate Sub # Zip Rate
$9.00 1486-10 76801 8.00
$9.00 1486-10 76802 8.00
$9.00 1486-10 76821 8.00
$9.00 1486-10 76823 8.00
$9.00 1486-10 76825 8.00
$9.00 1486-10 76837 8.00
$15.00 1486-10 76837 13.00
$9.00 1486-10 76864 8.00
$9.00 1486-10 76877 15.00
$9.00 1486-10 76903 8.00
$9.00 1486-10 76904 8.00
$9.00 1486-10 76905 8.00
$9.00 1486-10 76943 15.00
$9.00 1486-10 78643 15.00
$9.00 1486-10 79511 8.00
$9.00 1486-10 79512 8.00
$9.00 1486-10 79525 8.00
$9.00 1486-10 79536 7.00
$9.00 1486-10 79541 7.00
$9.00 1486-10 79549 8.00
$9.00 1486-10 79553 8.00
$9.00 1486-10 79556 8.00
$9.00 1486-10 79562 7.00
$9.00 1486-10 79601 7.00
$9.00 1486-10 79602 7.00
$9.00 1486-10 79603 7.00
$9.00 1486-10 79605 7.00
$9.00 1486-10 79606 7.00

Some zip codes are paid at different rates based on the zip code & the type of task to perform. For example, my rate for one zip code may vary based on the task. (see 76837 above) So it is feasable that one zip code may have different rates based on the task. Is there a way to have a macro apply the "If X is blank, then y = blank principle?" i.e. If column B (my rate) is "X" and column D (zip) is 79606 then column E = Sub's rate (based on type of task & zip)?

I am having to manually look at each task, zip & rate & set the sub's rate & it can be very tedious. I have tried to do this on my own but I just cannot get the hang of macros or formulas.

Thanks,

Bob

 

[SkipVought]

Values must match EXACTLY, with no leading or trailing spaces in one and not the other. Leading and trailing spaces ought to be routinely avoided. SINGLE space between words is not leading or trailing. Multiple spaces ought to be routinely avoided.

I get #N/A on some rows; rows 10 & 11, for instance. But most return values. Is your AutoCalculate on or hit F9 to calculate?

BTW, The MATCH function lookup range ought to start with H. This is not causing your #N/A.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bobnplano]

Skip,

Yes the AutoCalculate is on. I guess I just don't understand this. Is there a way to actually send you my spreadsheet so you can inspect it for errors?

Bob

 

[SkipVought]

skipandmary1017

at

mindspring

dot

com

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Please post back the moment you send.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bobnplano]

Skip,

I sent it with Rate Chart as Subject.

Bob

 

[SkipVought]

The Zip Code values that you have in column D, are TEXT.

The Zip Code values that you have in column H, are NUMBER, even though though FORMAT says TEXT. I determined it programatically.

Just changing the format does not chage anything. Each cell in column H must be edited.

Just F2, ENTER all the way down the column.



Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I get 2 #N/A's with your data.

Everything else resolves.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bobnplano]

Skip,

That is now working. Woooo Hoooo! Am I on the road to success? If so, thank you, thank you, thank you!

Bob

 

[SkipVought]

Your WORKBOOK sheet is sucessful. If that help you, then maybe you are.

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[CaptainD]

CaptainD (Programmer) 26 Apr 09 18:27

Also, your titles must match so in the lookup table (H2:N2) the title for Property Condition should be changed to Property Condition - No Contact

If the titles do not match, you will get #NA

After reading Skips comments, I remembered having to change one of your zip "Cell Values". I don't recall if it was text to number or the other way around.


Sorry I did not pass that on. Would have saved you some trouble.

Hope it works out for you

D

 

[bobnplano]

Thanks guys for staying with me & having patience with me. This has made my work much easier. I am so grateful to you.

Bob

 

[stevexff]

Now you have the data set up right, you can start using it for other purposes. The first thing that comes to mind is "when a new job comes in for a zip code, which subs have the lowest rate for this zip code?". Then you can subcontract the work in a way that means you don't lose money on the deal...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]