Need help with this formula in a macro, please
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 |
Need help with this formula in a macro, please
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 |
Need help with this formula in a macro, please
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 |
Need help with this formula in a macro, please
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 |
Need help with this formula in a macro, please
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 |
Need help with this formula in a macro, please
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 |
Need help with this formula in a macro, please
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 |
Need help with this formula in a macro, please
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 |
All times are GMT +1. The time now is 11:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com