Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveCell.FormulaR1C1
hi all.
I'm writing a macro that enters a formula or text values into a group of cells (if I've stated that wrong, sorry). I have 2 different elements that I want to enter. 1st is an IF equation-- =if(d9=k9,"ok",d9-k9) I've found that I'm not able to insert the " " for my true response, as it calls a "compile error." Is there a means by which I can reduce the amount of editing once the cell's contents are entered by the macro? I.e., I'd like to just have it input the equation, so that I don't need to add the two double quotes once the macro is complete. Or, if there is something else that will do the same thing as the two quotes around my true response, I'll do that. Ideas? 2nd is a sumproduct equation -- =sumproduct((ShtNm!CellRangeA&""=$A9&"")*(ShtNm!Ce llRangeB=$C9)*(ShtNm!CellRangeC)) I can enter everything here EXCEPT the two double quotes in the first array. Again, it gives me the compile error. Another option? Again, the idea is to minimize the amount of typing I have to do once the macro is complete. Thanks for the responses. Best to all. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveCell.FormulaR1C1
Just taking a guess try
Sub test() Range("A1").Value = "=IF(D9=K9,""ok"",D9-K9)" End Sub "SteveDB1" wrote: hi all. I'm writing a macro that enters a formula or text values into a group of cells (if I've stated that wrong, sorry). I have 2 different elements that I want to enter. 1st is an IF equation-- =if(d9=k9,"ok",d9-k9) I've found that I'm not able to insert the " " for my true response, as it calls a "compile error." Is there a means by which I can reduce the amount of editing once the cell's contents are entered by the macro? I.e., I'd like to just have it input the equation, so that I don't need to add the two double quotes once the macro is complete. Or, if there is something else that will do the same thing as the two quotes around my true response, I'll do that. Ideas? 2nd is a sumproduct equation -- =sumproduct((ShtNm!CellRangeA&""=$A9&"")*(ShtNm!Ce llRangeB=$C9)*(ShtNm!CellRangeC)) I can enter everything here EXCEPT the two double quotes in the first array. Again, it gives me the compile error. Another option? Again, the idea is to minimize the amount of typing I have to do once the macro is complete. Thanks for the responses. Best to all. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveCell.FormulaR1C1
Hi Steve
This will work: ActiveCell.Formula = "=if(d9=k9," & Chr$(34) & "ok" & Chr$(34) & ",d9- k9)" 2nd formula should be entered the same way. Regards, Per On 28 Mar., 00:44, SteveDB1 wrote: hi all. I'm writing a macro that enters a formula or text values into a group of cells (if I've stated that wrong, sorry). I have 2 different elements that I want to enter. 1st is an IF equation-- =if(d9=k9,"ok",d9-k9) I've found that I'm not able to insert the " " for my true response, as it calls a "compile error." Is there a means by which I can reduce the amount of editing once the cell's contents are entered by the macro? I.e., I'd like to just have it input the equation, so that I don't need to add the two double quotes once the macro is complete. Or, if there is something else that will do the same thing as the two quotes around my true response, I'll do that. Ideas? 2nd is a sumproduct equation -- =sumproduct((ShtNm!CellRangeA&""=$A9&"")*(ShtNm!Ce llRangeB=$C9)*(ShtNm!Cell*RangeC)) I can enter everything here EXCEPT the two double quotes in the first array. Again, it gives me the compile error. Another option? Again, the idea is to minimize the amount of typing I have to do once the macro is complete. Thanks for the responses. Best to all. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveCell.FormulaR1C1
Per,
This does appear to work, but I then get something I didn't expect. the print out of the equation changes to now include single quotes around each cell call. =IF('f9'='o9',"ok",'f9'-'o9') I guess this is what they mean by the law of unintended consequences. And no, I didn't place the quotes in the code. ActiveCell.FormulaR1C1 = "=if(f9=o9," & Chr(34) & "ok" & Chr(34) & ",f9-o9)" Is there any way to prevent the placement of the single quotes into the output? "Per Jessen" wrote: Hi Steve This will work: ActiveCell.Formula = "=if(d9=k9," & Chr$(34) & "ok" & Chr$(34) & ",d9- k9)" 2nd formula should be entered the same way. Regards, Per On 28 Mar., 00:44, SteveDB1 wrote: hi all. I'm writing a macro that enters a formula or text values into a group of cells (if I've stated that wrong, sorry). I have 2 different elements that I want to enter. 1st is an IF equation-- =if(d9=k9,"ok",d9-k9) I've found that I'm not able to insert the " " for my true response, as it calls a "compile error." Is there a means by which I can reduce the amount of editing once the cell's contents are entered by the macro? I.e., I'd like to just have it input the equation, so that I don't need to add the two double quotes once the macro is complete. Or, if there is something else that will do the same thing as the two quotes around my true response, I'll do that. Ideas? 2nd is a sumproduct equation -- =sumproduct((ShtNm!CellRangeA&""=$A9&"")*(ShtNm!Ce llRangeB=$C9)*(ShtNm!CellĀ*RangeC)) I can enter everything here EXCEPT the two double quotes in the first array. Again, it gives me the compile error. Another option? Again, the idea is to minimize the amount of typing I have to do once the macro is complete. Thanks for the responses. Best to all. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveCell.FormulaR1C1
My next question is:
How would I set multiple character placements? I tried &chr()& &chr()& &Chr() and it immediately calls a compile error. When I just set a single & inbetween & chr(n) & chr(n) & chr(n) & where n is some value, it calls a debug 1004 define object error. I then tried a comma inbetween & chr(), chr(), chr() & and got a compile error saying it expected a statement, and I then tried no & in between character calls & chr() chr() chr() & and got a compile error, saying it expected a statement. Again, thanks for the responses. "Per Jessen" wrote: Hi Steve This will work: ActiveCell.Formula = "=if(d9=k9," & Chr$(34) & "ok" & Chr$(34) & ",d9- k9)" 2nd formula should be entered the same way. Regards, Per On 28 Mar., 00:44, SteveDB1 wrote: hi all. I'm writing a macro that enters a formula or text values into a group of cells (if I've stated that wrong, sorry). I have 2 different elements that I want to enter. 1st is an IF equation-- =if(d9=k9,"ok",d9-k9) I've found that I'm not able to insert the " " for my true response, as it calls a "compile error." Is there a means by which I can reduce the amount of editing once the cell's contents are entered by the macro? I.e., I'd like to just have it input the equation, so that I don't need to add the two double quotes once the macro is complete. Or, if there is something else that will do the same thing as the two quotes around my true response, I'll do that. Ideas? 2nd is a sumproduct equation -- =sumproduct((ShtNm!CellRangeA&""=$A9&"")*(ShtNm!Ce llRangeB=$C9)*(ShtNm!CellĀ*RangeC)) I can enter everything here EXCEPT the two double quotes in the first array. Again, it gives me the compile error. Another option? Again, the idea is to minimize the amount of typing I have to do once the macro is complete. Thanks for the responses. Best to all. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ActiveCell.FormulaR1C1 | Excel Discussion (Misc queries) | |||
ActiveCell.FormulaR1C1 | Excel Programming | |||
ActiveCell.FormulaR1C1 | Excel Programming | |||
activecell.formular1c1 | Excel Programming | |||
ActiveCell.FormulaR1C1 | Excel Programming |