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!CellRangeB=$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.

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!CellRangeB=$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.
>

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 <Steve...@discussions.microsoft.com>
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 macrois
> 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!CellRangeB=$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.

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 <Steve...@discussions.microsoft.com>
> 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!CellRangeB=$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.
>
>

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 <Steve...@discussions.microsoft.com>
> 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!CellRangeB=$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.
>
>