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

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" <(E-Mail Removed)> wrote in message
news:OG1B$(E-Mail Removed)...
> 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
>

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

"Jim Rech" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:OG1B$(E-Mail Removed)...
>> 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
>>
>
>