"Object required" error when setting Excel formula 2

[hazelsisson]

Hi!
I'm having trouble trying to set formulae in a range of Excel cells using this code:

Sub p1formula()
    For i = 10 To 12
        Dim Address, myFormula
        Address = "EX" & i
        jref = "Sheet1!J" & i
        rref = "Sheet1!R" & i
        
        myFormula = "=SUM(INDIRECT(" & jref & ")+INDIRECT(" & rref & "))"
        Response = MsgBox(myFormula)
        Set Address.Formula = (myFormula)
    Next i
End Sub

The MsgBox pops up correctly, so it seems to be the last line (Set Address.Formula = (myFormula)) that's causing problems. It's the Object required (Error 424) error that occurs.
Any ideas?
Thanks,
Hazel

 

[Chance1234]

Sub p1formula()
    For i = 10 To 12
        Dim Address, myFormula
        Address = "EX" & i
        jref = "Sheet1!J" & i
        rref = "Sheet1!R" & i
        
        myFormula = "=SUM(INDIRECT(" & jref & ")+INDIRECT(" & rref & "))"
        Response = MsgBox(myFormula)
     '   Set Address.Formula = (myFormula)
        Sheet2.Range(Address).Formula = myFormula
    Next i
End Sub

replace sheet2 with whatever sheet you are using

Filmmaker, gentleman and pearls before swine fan

http://WWW.5YLAC.S5.COM

 

[MakeItSo]

Two things:
a) SET always refers to an object
b) YOu need to tell Excel that EX10... is a cell:
So instead of the line Set Address.Formula = (myFormula)
you need

Range(Address).Formula=myFormula

Best wishes,
Andy

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[MakeItSo]

Oh, you been faster, Chance... :-D

 

[hazelsisson]

Excellent, it worked.
Thank-you both for your replies!
Hazel