Absolute reference
 

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

Absolute reference
 

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
"Sandy" wrote in message
...
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

Absolute reference
 

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

"Jim Rech" wrote in message
...
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
"Sandy" wrote in message
...
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