Need help with this formula in a macro, please

  • Thread starter Thread starter Roger
  • Start date Start date
R

Roger

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
 
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))))"
 
The macro is reading part numbers. For eaxmple col B might have a part
number like: U60-04586-1A252, so the U is text.
 
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))))"
 
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.
 
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))))"
 
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
 
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
 
Back
Top