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