Macro If/then statement expression error

R

rexie3

I'm writing a macro that will identify the plant's ID by looking at a cell
(A9).

My if statement has an error and I can't figure out what is wrong with it.
The error is somewhere here and it says I have a "compile error: syntax
error". I have 7 nested statements. Here's the formula:

ActiveCell.FormulaR1C1 = _
"=IF(A9="Shipto : 213455 LEAR CORPORATION-CARDL","LEAR PLANT:
MX02M037 - OSCAR",IF(A9="Shipto : 233783 LEAR CORP ESD","LEAR PLANT: MX02AZ79
- ARLIN",IF(A9="Shipto : 241885 LEAR CORPORATION PLANTA 160","LEAR PLANT:
MX02M037 - OSCAR",IF(A9="Shipto : 233782 LEAR CORPORATION ESD","LEAR PLANT:
MX02AZ79 - ARLIN",IF(A9="Shipto : 239129 LEAR PLANT 164","LEAR PLANT:
MX02AZ79 - ARLIN",IF(A9="Shipto: 213454 LEAR CORPORATION-CARDL","LEAR PLANT:
MX02PI42 - SAUL",IF(A9="Shipto : 229483 LEAR MXO2P142","LEAR PLANT: MX02PI42
- SAUL","0")))))))"

Also how do I get this process to go down the report looking for "Shipto :
2" (in the cell A#) and putting the plant ID in the cell F#? I have to run
this daily and some days the report is long and others they are not.

Thank you so much! I've been at this for a few days now.
 
C

Carl Witthoft

rexie3 said:
I'm writing a macro that will identify the plant's ID by looking at a cell
(A9).

My if statement has an error and I can't figure out what is wrong with it.
The error is somewhere here and it says I have a "compile error: syntax
error". I have 7 nested statements. Here's the formula:

ActiveCell.FormulaR1C1 = _
"=IF(A9="Shipto : 213455 LEAR CORPORATION-CARDL","LEAR PLANT:
MX02M037 - OSCAR",IF(A9="Shipto : 233783 LEAR CORP ESD","LEAR PLANT: MX02AZ79
- ARLIN",IF(A9="Shipto : 241885 LEAR CORPORATION PLANTA 160","LEAR PLANT:
MX02M037 - OSCAR",IF(A9="Shipto : 233782 LEAR CORPORATION ESD","LEAR PLANT:
MX02AZ79 - ARLIN",IF(A9="Shipto : 239129 LEAR PLANT 164","LEAR PLANT:
MX02AZ79 - ARLIN",IF(A9="Shipto: 213454 LEAR CORPORATION-CARDL","LEAR PLANT:
MX02PI42 - SAUL",IF(A9="Shipto : 229483 LEAR MXO2P142","LEAR PLANT: MX02PI42
- SAUL","0")))))))"

Also how do I get this process to go down the report looking for "Shipto :
2" (in the cell A#) and putting the plant ID in the cell F#? I have to run
this daily and some days the report is long and others they are not.

Thank you so much! I've been at this for a few days now.
Click to expand...

I suppose it wouldn't do much good to suggest writing clean code :-(
First: does this formula (exactly as written - do a Copy / Paste) work
in a worksheet cell? If not, then it needs fixing...

Next: consider using CHOOSE. You should have set up the spreadsheet
correctly in the first place, which is to say the number, "lear corp,"
"-name" should all be separate columns. (actually you should be using
a database tool, but that's a whole 'nother headache). Since we're
stuck with a long string, use LEFT function to extract the number. Map
the numbers to some index (numbers 1 up to 29), and apply the CHOOSE
function appropriately.

In any case, if this is all you're doing, you don't need a macro. Cell
formulas will do just fine.
 
R

rexie3

Hello Carl,

I will certainly look into using CHOOSE. The reason why I chose a macro is
because I'm not the only user doing this report. The formula works fine if I
copy and paste from cell to cell but it's not working in a macro environment
for some reason.

I'm using a macro because the formula has to be transferrable and as error
proof as possible. I have found that it is more effective to distribute a
macro in a network and create a button in the user's workstation. Things get
very interesting when a user is not excel savvy and you try and get them to
use formulas. ; - )

I'm not an excel expert so if there's anything you can advise on how to
write this in macro, I'd really appreciate it.
Thanks!
 
Top