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
