Extending the range

0

In relation to the question in 

https://www.teachexcel.com/talk/3398/sheet-tab-in-formula

The answer from variatus is perfect. Here, I tried to include calculation for other cells (in the same column) like B6, B7 and so on with the same code but it only added all values in B5 instead of giving a separate result in B6 or B7 in the first sheet.

So, what should be added to achieve this purpose or should this code be repeated for each cell?

Thanks!

Answer
Discuss

Discussion

The UDF looks like it is only supposed to return a single value into a single cell anyway; maybe I'm just not understanding exactly what you are trying to do, but note that that UDF was made very specifically to help with the question that was asked. Can you show us the code and formula that you are working with so far and maybe an example?
don (rep: 1655) Feb 27, '19 at 8:29 am
Thanks sir for your response. I've selected the answer.
Asim (rep: 2) Feb 27, '19 at 11:37 pm
Add to Discussion

Answers

0
Selected Answer

In the variation of my earlier UDF, to which your question refers, you can specify the cell to be summed in the function call. Call the function like =Sum3D("B5") to sum up the values in B5 in all sheets between (and including) the sheets specified in A1:B1. You can specify any single cell or even a range of cells.

Function Sum3D(CellAddress As String) As Variant
    ' this function will return -1 if the sheet in Sheet1!A1 doen't exist.
    ' It will return the sum of all sheet from Sheet1!A1 to end if Sheet1!B1 doesn't exist

    ' observe the addressing of "Sheet1" by its CodeName
    ' rather than its name, like in Worksheets("Sheet1").
    ' This has the effect that you can change the name of this
    ' sheet without needing to change the code.

    Dim Fun As Double
    Dim FromWs As String
    Dim ToWs As String
    Dim i As Integer

    Application.Volatile
    FromWs = Sheet1.Cells(1, "A").Value
    ToWs = Sheet1.Cells(1, "B").Value

    On Error GoTo ErrExit
    For i = Worksheets(FromWs).Index To Worksheets.Count
        Fun = Fun + Val(Worksheets(i).Range(CellAddress).Value)
        If Worksheets(i).Name = ToWs Then Exit For
    Next i
    Sum3D = Fun
    Exit Function

ErrExit:
    Sum3D = -1              ' change this to "ERROR" if you prefer
End Function

Note that the earlier function returns an integer which I changed here to become a Variant of Double format. Therefore this function can add up money correctly, for example, or even scientific numbers. In case of error in A1:B1 you can make this function return text, like "Missing Tab" in place of the simple "-1" currently programmed above as inherited from the previous UDF.

Discuss

Discussion

Thanks Sir, for your perfect answer!
Asim (rep: 2) Feb 27, '19 at 11:35 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login