none
how do i add a data to a formula in VBA? RRS feed

  • Question

  • I have a cell which calculates the average value of a specific data range on multiple worksheets:

    =AVERAGE('120618'!B3:B17,'120718'!B3:B17, etc.)

    I want to write a macro that will take that range on a newly created worksheet and add it to the data string inside the AVERAGE function like this:

    =AVERAGE('120618'!B3:B17,'120718'!B3:B17, 'new sheet'!B3:B17)

    When i record the macro, it simply takes the function in the state the macro was recorded and adds the new sheet. But then when another new sheet is created, the macro overwrites the previous sheet. Is there a way to have it just insert the range at the end of the current state of the function?

    here is what the recorder spits out:

    Sub update

    Range("C8").Select
        ActiveCell.FormulaR1C1 = _
            "=AVERAGE('120618'!R[-3]C[2]:R[9]C[2],'120718'!R[-5]C[1]:R[8]C[1],'new sheet'!R[-5]C[1]:R[8]C[1])" & _
            ""

    End sub

    Tuesday, March 19, 2019 7:18 PM

Answers

  • Here you go:

    Sub update()
        Dim strFormula As String
        Dim strSheet As String
        strFormula = Range("C8").Formula
        strSheet = Worksheets(Worksheets.Count).Name
        Range("C8").Formula = Replace(strFormula, ")", _
            ",'" & strSheet & "'!B3:B17)")
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Ksack85 Thursday, March 21, 2019 3:41 PM
    Tuesday, March 19, 2019 8:34 PM

All replies

  • Here you go:

    Sub update()
        Dim strFormula As String
        Dim strSheet As String
        strFormula = Range("C8").Formula
        strSheet = Worksheets(Worksheets.Count).Name
        Range("C8").Formula = Replace(strFormula, ")", _
            ",'" & strSheet & "'!B3:B17)")
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Ksack85 Thursday, March 21, 2019 3:41 PM
    Tuesday, March 19, 2019 8:34 PM
  • thank you! this worked!
    Tuesday, March 19, 2019 9:14 PM