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.

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.

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.

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.

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.