Create formulas with $Rn1Cn2:$Rn1Cn3 format
 

I need to have VBA create formulas with mixed referencing such as:

=SUM($G10:$G20)

I can create =SUM($G$10:$G$20) and =SUM(G10:G20), neither of which is what I
need.

Thanks,
Mike.

Create formulas with $Rn1Cn2:$Rn1Cn3 format
 

Mike,

Have you tried something like

ActiveCell.FormulaR1C1 = "=SUM(R[10]C7:R[20]C7)"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Michael D. Ober" <[email protected] wrote in message
...
I need to have VBA create formulas with mixed referencing such as:

=SUM($G10:$G20)

I can create =SUM($G$10:$G$20) and =SUM(G10:G20), neither of which is what
I
need.

Thanks,
Mike.

Create formulas with $Rn1Cn2:$Rn1Cn3 format
 

Sub makeformula()
[n5].Formula = "=SUM($G10:$G20)"
'[n5].Formula =[n5].value'to chg to value
End Sub


--
Don Guillett
SalesAid Software

"Michael D. Ober" <[email protected] wrote in message
...
I need to have VBA create formulas with mixed referencing such as:

=SUM($G10:$G20)

I can create =SUM($G$10:$G$20) and =SUM(G10:G20), neither of which is what
I
need.

Thanks,
Mike.

Create formulas with $Rn1Cn2:$Rn1Cn3 format
 

Sorry, You said r1c1

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
Sub makeformula()
[n5].Formula = "=SUM($G10:$G20)"
'[n5].Formula =[n5].value'to chg to value
End Sub


--
Don Guillett
SalesAid Software

"Michael D. Ober" <[email protected] wrote in message
...
I need to have VBA create formulas with mixed referencing such as:

=SUM($G10:$G20)

I can create =SUM($G$10:$G$20) and =SUM(G10:G20), neither of which is
what
I
need.

Thanks,
Mike.

Create formulas with $Rn1Cn2:$Rn1Cn3 format
 

sStr = "=SUM(G10:G20)"
? application.ConvertFormula(sStr,xlA1,xlA1,xlAbsRow RelColumn)
=SUM(G$10:G$20)
? application.ConvertFormula(sStr,xlA1,xlA1,xlRelRow AbsColumn)
=SUM($G10:$G20)

--
Regards,
Tom Ogilvy


Michael D. Ober <[email protected] wrote in message
...
I need to have VBA create formulas with mixed referencing such as:

=SUM($G10:$G20)

I can create =SUM($G$10:$G$20) and =SUM(G10:G20), neither of which is what
I
need.

Thanks,
Mike.