Find a cell meeting these conditions (Excel 2000)

Topic

New Reply

All,
Is there a formula to find the last non-zero cell in a row where the cell values are calculated with formulas and display the results in another cell?
Thanks

Reply | Quote

Replies

For example, to return the last non-blank number in row 16:

=INDEX(16:16,MATCH(9.99999999999999E+307,16:16))

If you want to exclude zero values, I’ll have to think about it.

Reply | Quote

Hans,
I found that example on the web and the problem is a zero value.
The row has 12 columns(months) plus a description in the first column.
As they fill in the template sheet every month, the next column of the row(data i’m looking for) will data in it.
I’m using this function to calculate the values in the cells

Function SumAll(sCell As String)
    Dim wks As Worksheet
    Dim dSum As Double
    Dim iCount As Intege

    Application.Volatile
    iCount = 0
    dSum = 0
    For Each wks In Worksheets
        dSum = dSum + Application.Sum(wks.Range(sCell))
    Next
        SumAll = dSum
    Set wks = Nothing
End Function

Should this be modified to make your formula work?

Reply | Quote

The SumAll function is intended to sum a cell (or range) across all worksheets in a workbook. I don’t see what that has to do with your original question.

Reply | Quote

Hans,
I included the Function to show you how the cells in the row get their value.
I modified the Function so if there isn’t any sum involved, it won’t place a zero in the cell.

Function SumAll(sCell As String)
    Dim wks As Worksheet
    Dim dSum As Double
    Dim iCount As Integer

    Application.Volatile
    iCount = 0
    dSum = 0
    For Each wks In Worksheets
        dSum = dSum + Application.Sum(wks.Range(sCell))
    Next
        If dSum = 0 Then
            SumAll = ""
        Else
            SumAll = dSum
        End If
    Set wks = Nothing
End Function

Then using your original formula, I can achieve the results I want.

Reply | Quote

Oh, OK. Glad you were able to solve it.

Reply | Quote