| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Possible Macro or formula | Excel Discussion (Misc queries) | |||
| Formula Macro | Excel Discussion (Misc queries) | |||
| set formula by Macro | Excel Worksheet Functions | |||
| Do I need a formula or Macro? | Excel Worksheet Functions | |||
| how to run a macro from a formula? | Excel Discussion (Misc queries) | |||
Linear Mode
