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 said:
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
