it was probably the word wrap, you would have needed to look at the
underscore character and end the line after each one in my example,
glad you got it working


--


Gary


"Roger" wrote in message
...
Well, I got the same error message, but I finally got it to work by using:

ActiveCell.Formula = "=if(MID(B4,1,2)=" & 29 &
",MID(B4,4,5),IF(MID(B4,1,2)=" _
& 20 & ",MID(B4,4,5),IF(MID(B4,1,1)= _
""U"",MID(B4,5,4),MID(B4,5,4))))"

I got it thanks to your help though, by playing around with the double
quotes. I always get tripped up by quotes in macro formulas.

Thanks for your help, have a great night,

Roger

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
try this

ActiveCell.Formula = "=if(MID(B4,1,2)=" & 29 & _
",MID(B4,4,5),IF(MID(B4,1,2)=" & 20 & ",MID(B4,4,5),IF(MID(B4,1,1)=" &
"""U""" & _
",MID(B4,5,4),MID(B4,5,4))))"

--


Gary


"Roger" wrote in message
...
I am getting a run time error 1004 application-defined or object defined
error. If the formula is entered directly into the cell the U will have a
quote around it "U". I can get the macro to enter the formula so that the
U has a quote around it. That is my only problem. It's simple but I can
not it straight.

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
then it probably should have quotes around it. what error are your
getting
ActiveCell.Formula = "=if(MID(B4,1,2)=" & 29 & _
",MID(B4,4,5),IF(MID(B4,1,2)=" & 20 & ",MID(B4,4,5),IF(MID(B4,1,1)=" &
"U" & _
",MID(B4,5,4),MID(B4,5,4))))"




--


Gary


"Roger" wrote in message
...
The macro is reading part numbers. For eaxmple col B might have a part
number like: U60-04586-1A252, so the U is text.


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
what's the value of U at the end of the 2nd row? is it text, ot a
variable?

ActiveCell.Formula = "=if(MID(B4,1,2)=" & 29 & _
",MID(B4,4,5),IF(MID(B4,1,2)=" & 20 & ",MID(B4,4,5),IF(MID(B4,1,1)="
& U & _
",MID(B4,5,4),MID(B4,5,4))))"


--


Gary


"Roger" wrote in message
...
I can not figure out what is wrong with the following formula when
used in a macro:

ActiveCell.Formula = "=if(MID(B4,1,2)=" & 29 &
",MID(B4,4,5),IF(MID(B4,1,2)=" & 20 & ",MID(B4,4,5),IF(MID(B4,1,1)="
& U & ",MID(B4,5,4),MID(B4,5,4))))"

I am using XL 2000 w/ XP.

Thanks for any help,

Roger