Display last number.

[kc27315]

I'm attatching a spreadsheet to show what I'm trying to do. In column B from row 1 to row 26, I have formulas that are dependent on wether I type a "1" in the corresponding row of column A. If there's a "1" in column A of a row, then the corresponding row in column B displays a number, otherwise it displays the text "". I type in the number "1" starting in row 1 and proceeding downward.

What I want is for cell D1 to display the last number from column B. I currently have "=B13" typed into cell D1, so it displays 4.76. That does me no good though because if I type a 1 in A14, I want D1 to display "5.32".

Does anyone have any ideas on a function that will display the last number from rows 1 to 26 that is not text?

Thanks, kc

[Trebor76]

Hi kc27315,

Here's a UDF I wrote to do the job:

Function LastVal(rng As Range, val As Integer) As Currency

'This UDF displays the last (visible) value in an adjacent right column based on _
the last val and blank cell in a selected column. _
Note that it could be easily modified to simply work on a selected column.

For Each Cell In rng
    If Cell.Value = val And Cell.Offset(1, 0) = "" Then
        LastVal = Cell.Offset(, 1).Value
        Exit For
    End If
Next Cell

End Function

Forgive me if you already know how to do this, but the following five steps will put the code into a module that you can then run the function from:

1. Copy (Ctrl + C) my code
2. Open the VBA editor (Alt + F11)
3. From the Insert menu click Module
4. Paste (Ctrl + V) my code from step 1 above into the blank module
5. From the File menu click Close and Return to Microsoft Excel

Then use the UDF as you would any other native excel formula - i.e. =LastVal(A1:A26,1)

HTH

Robert

[kc27315]

Thanks so much for figuring out the VBA trick for me. I usually try to stay away from VBA, since I'm a bit uncomfortable with it. Although I sometimes use it. I figured out a workaround using column F (I think it's F). I've attatched a spreadsheet that shows how I did it.

Thanks again for the reply. You people that know VBA so well are amazing.

kc