Create formulas with $Rn1Cn2:$Rn1Cn3 format

  • Thread starter Thread starter Michael D. Ober
  • Start date Start date
M

Michael D. Ober

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.
 
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)
 
Sub makeformula()
[n5].Formula = "=SUM($G10:$G20)"
'[n5].Formula =[n5].value'to chg to value
End Sub
 
Sorry, You said r1c1

--
Don Guillett
SalesAid Software
(e-mail address removed)
Don Guillett said:
Sub makeformula()
[n5].Formula = "=SUM($G10:$G20)"
'[n5].Formula =[n5].value'to chg to value
End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)
Michael D. Ober said:
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
Click to expand...
what
I
need.

Thanks,
Mike.
Click to expand...
Click to expand...
 
sStr = "=SUM(G10:G20)"
? application.ConvertFormula(sStr,xlA1,xlA1,xlAbsRowRelColumn)
=SUM(G$10:G$20)
? application.ConvertFormula(sStr,xlA1,xlA1,xlRelRowAbsColumn)
=SUM($G10:$G20)
 
Back
Top