• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Average Formula With Variables

Octofez2

New Member
Good Morning All,

What I am trying to accomplish is I have a cell range that I would like to use as sheet names. I have an Averages worksheet at the end of the workbook and have roughly 50 sheets in front. I'd like to get the averages for the last 6 sheets which is before the Averages sheet. The formula below has been shortened for testing.The Msgbox populates the correct sheet name based on the variable but I can't get the formula to work. I get an error "Object doesn't support this property or method."

Thanks for taking the time to look this over.

Code:
Private Sub btnGetAverages_Click()

        Dim rngM3 As String, shtM3 As Worksheet
        rngM3 = Sheets("Averages").Range("M3").Value
        Set shtM3 = Sheets(rngM3)
       
        Dim rngM4 As String, shtM4 As Worksheet
        rngM4 = Sheets("Averages").Range("M4").Value
        Set shtM4 = Sheets(rngM4)
       
   
       With Worksheets("Averages")
       
            'MsgBox shtM3.Name
            'Exit Sub

            .Range.Formula = _
                "=AVERAGE(" & shtM3 & "!D3," & shtM4 & "!D3)"
               
            'Debug.Print shtM3
               
       End With
       

End Sub
 
Upload sample workbook. Syntax looks off. But without knowing what's actually in your sheet, hard to say exactly what changes need to be made in your code.
 
Ok need bit more clarification.

Do you want to average D3 of all sheets found in M3:M8 in C2 of "Averages" sheet?

What's the final output that you want to produce?
 
If it's always going to be all sheets from first listed sheet to last listed sheet...

You can do something like...
Code:
Sub Get_Average()
Dim startSh As String
Dim endSh As String

startSh = Range("M3").Text
endSh = Range("M" & Cells(Rows.Count, "M").End(xlUp).Row).Text

Range("C2").Formula = "=AVERAGE('" & startSh & ":" & endSh & "'!D3)"

End Sub

Though I'd it bit differently depending on what's final output you want to produce.
 
Thank you very much. I like it and it may do the trick. It's a lot simpler than I anticipated. I'll play around with the code.
 
Back
Top