Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing Excel formulas in macros - Help!!
I am attempting to use a macro to write a formula in to a blank cell. When
the formula is written, the cell reference in the formula has apostrophes around it so it won't function. I'm sure there is a syntax step I'm missing. Can someone help?? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing Excel formulas in macros - Help!!
hi
post your code Regards FSt1 "Mark Lucas" wrote: I am attempting to use a macro to write a formula in to a blank cell. When the formula is written, the cell reference in the formula has apostrophes around it so it won't function. I'm sure there is a syntax step I'm missing. Can someone help?? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing Excel formulas in macros - Help!!
I bet you're using .formulaR1C1, but you're creating the formula using A1
reference style. Maybe you can use .formula (or actually change the formula so that it uses R1C1 reference style). somecell.formulaR1C1 = "=...." becomes somecell.formula = "=...." Mark Lucas wrote: I am attempting to use a macro to write a formula in to a blank cell. When the formula is written, the cell reference in the formula has apostrophes around it so it won't function. I'm sure there is a syntax step I'm missing. Can someone help?? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing Excel formulas in macros - Help!!
ActiveCell.FormulaR1C1 = "=IF(MID(b2,8,3)=""CFD"",1,0)" is the code in the
macro =IF(MID('b2',8,3)="CFD",1,0) is what shows up in the cell. "FSt1" wrote: hi post your code Regards FSt1 "Mark Lucas" wrote: I am attempting to use a macro to write a formula in to a blank cell. When the formula is written, the cell reference in the formula has apostrophes around it so it won't function. I'm sure there is a syntax step I'm missing. Can someone help?? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing Excel formulas in macros - Help!!
Hey that worked!!!! Thanks! So what is the difference between the styles?
"Dave Peterson" wrote: I bet you're using .formulaR1C1, but you're creating the formula using A1 reference style. Maybe you can use .formula (or actually change the formula so that it uses R1C1 reference style). somecell.formulaR1C1 = "=...." becomes somecell.formula = "=...." Mark Lucas wrote: I am attempting to use a macro to write a formula in to a blank cell. When the formula is written, the cell reference in the formula has apostrophes around it so it won't function. I'm sure there is a syntax step I'm missing. Can someone help?? -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing Excel formulas in macros - Help!!
Wait there's more ... I want the cell reference to change as that formula is
being copied down the column. How can I write that in to the formula? "FSt1" wrote: hi post your code Regards FSt1 "Mark Lucas" wrote: I am attempting to use a macro to write a formula in to a blank cell. When the formula is written, the cell reference in the formula has apostrophes around it so it won't function. I'm sure there is a syntax step I'm missing. Can someone help?? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing Excel formulas in macros - Help!!
Try toggle the setting and looking at a few formulas:
tools|options|General tab|check R1C1 reference style. You'll notice that the columns are now numbered--not lettered. And your formulas look like: =if(r2c1="asdf","ok","not ok") instead of =if($A$2="asdf","ok","not ok") Remember to toggle this setting back. Mark Lucas wrote: Hey that worked!!!! Thanks! So what is the difference between the styles? "Dave Peterson" wrote: I bet you're using .formulaR1C1, but you're creating the formula using A1 reference style. Maybe you can use .formula (or actually change the formula so that it uses R1C1 reference style). somecell.formulaR1C1 = "=...." becomes somecell.formula = "=...." Mark Lucas wrote: I am attempting to use a macro to write a formula in to a blank cell. When the formula is written, the cell reference in the formula has apostrophes around it so it won't function. I'm sure there is a syntax step I'm missing. Can someone help?? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
writing excel macros | Excel Programming | |||
writing macros in excel sheet | Excel Worksheet Functions | |||
writing macros in excel | Excel Programming | |||
Writing Excel Macros in VB.NET/C# | Excel Programming | |||
Writing Macros in Excel | Excel Programming |