Absolute reference

[Sandy]

How can the following be converted to an absolute reference

FormulaR1C1 =
"=SUMPRODUCT(--(R[5]C[-54]:R[30006]C[-54]=""Ball""),--(R[5]C:R[30006]C=""Hit""))"

TIA
Sandy

 

[Jim Rech]

It's impossible to convert the cell references to absolute references
without know what cell this formula is going in.

So here's how you can do the conversion:
-In a new worksheet switch to the R1C1 reference style (Tools, Options,
General).
-Copy the formula
("=SUMPRODUCT(--(R[5]C[-54]:R[30006]C[-54]=""Ball""),--(R[5]C:R[30006]C=""Hit""))")
into its intended cell (the one the macro would put it in).
-Convert this to a formula from text by removing the extra quotes (at the
beginning, end and around 'Ball' and 'Hit').
-Switch Excel out of the R1C1 reference style.
-Make all the cell references in the formula absolute by putting "$" before
every column letter and row number (e.g., T17 - > $T$17).
-Switch back to the R1C1 reference style.
-Copy the resulting formula to VB, adding back the extra quotes it needs.

 

[Sandy]

Excellent Jim, I understand how it all works much better now
Thank You
Sandy

It's impossible to convert the cell references to absolute references
without know what cell this formula is going in.

So here's how you can do the conversion:
-In a new worksheet switch to the R1C1 reference style (Tools, Options,
General).
-Copy the formula
("=SUMPRODUCT(--(R[5]C[-54]:R[30006]C[-54]=""Ball""),--(R[5]C:R[30006]C=""Hit""))")
into its intended cell (the one the macro would put it in).
-Convert this to a formula from text by removing the extra quotes (at the
beginning, end and around 'Ball' and 'Hit').
-Switch Excel out of the R1C1 reference style.
-Make all the cell references in the formula absolute by putting "$"
before every column letter and row number (e.g., T17 - > $T$17).
-Switch back to the R1C1 reference style.
-Copy the resulting formula to VB, adding back the extra quotes it needs.
--
Jim

How can the following be converted to an absolute reference

FormulaR1C1 =
"=SUMPRODUCT(--(R[5]C[-54]:R[30006]C[-54]=""Ball""),--(R[5]C:R[30006]C=""Hit""))"

TIA
Sandy