How do you get a defined range address to show up as a relative reference in formula?

Question

How I can get the the range address for rng3 to show up in my sum formula as a relative reference (ex. B2:B4) instead of as the range name?

The sum formula I am trying to use it for is giving a value of 0.

I am trying to copy the formula down the rest of column D.

It is for a range based on the last column in the spreadsheet that changes every week.

Sub needhelp ()
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim fillrange As Range
Dim cell1 As Range
Dim cell2 As Range
Dim cellAddress As Range

    Range("G1").Select
    Range("G1").End(xlToRight).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.Offset(0, -2).Select
    Set cell1 = ActiveCell
    Set rng1 = Cells.Find("*", [cell1], , , xlByColumns, xlNext)
    ActiveCell.Offset(0, 2).Select
    Set cell2 = ActiveCell
    Set rng2 = Cells.Find("*", [cell2], , , xlByColumns, xlPrevious)
    If Not rng2 Is Nothing Then
        Set rng3 = Range([cell1], [cell2])
        MsgBox "Range is " & rng3.Address(0, 0)
        Application.Goto rng3
        MsgBox "Range is " & rng3.Address(0, 0)
        Application.Goto rng3
        Range("D2").Select

'    *** Here is when I need help.

        Worksheets("Confidential").Range("D2").Formula = "=SUM(rng3) / 3"
        ActiveCell.Offset(0, -1).Select
        Selection.End(xlDown).Select
        ActiveCell.Offset(0, 1).Select
        Range(Selection, Selection.End(xlUp)).Select    
       Cells.Select
       Cells.EntireColumn.AutoFit
    End If

End Sub

I also tried the following (did not work):

'        Debug.Print Range("rng4").Address(External:=True)

'        Try to use this to fill average range with formula using loop (some other time):


'        ActiveWorkbook.Names.Add Name:="fillrange", RefersTo:=Selection

'        Range("D2").Select

'    ActiveWorkbook.Names.Add Name:="", RefersTo:=Selection

Answer 1

Your formula should be:

"=SUM(" & rng3.Address(0,0) & ") / 3"

You need to quote out the vba.

Also, all the .Select slow down the code. See THIS POST for methods to avoid using it. It will speed up the code.