L
LCproj
Hi,
Here is my example:
The situation:
I have a table where we add numbers at the bottom. (bottom is most
recent) I want to find the most recent number of row B (calling it
FindLastEntry2)(done by the function below)
---> I want to output the number x in column A corresponding to the
same row as the the FindLastEntry2 number.
Example:
A B
1 23
2 42
1
3 8
4 44
4
------------------------
Function FindLastEntry2(rng)
On Error Resume Next 'ignore errors if input is not a range
nRows = rng.Rows.Count 'count rows in input range
nCols = rng.Columns.Count 'count columns in input range
If nCols = 1 Then 'if one column, search rows
n = nRows
Do Until rng.Cells(n, 1).Value <> 0 Or n = 1
'loop from last row up until non zero found or top of range
n = n - 1
Loop
FindLastEntry2 = rng.Cells(n, 1).Value 'return value found
ElseIf nRows = 1 Then 'if one row search columns
n = nCols
Do Until rng.Cells(1, n).Value <> 0 Or n = 1
n = n - 1
Loop
FindLastEntry2 = rng.Cells(n, 1).Value
Else ' if not a single row or column, return message
FindLastEntry2 = "Please specify a single row or column"
End If
End Function
Here is my example:
The situation:
I have a table where we add numbers at the bottom. (bottom is most
recent) I want to find the most recent number of row B (calling it
FindLastEntry2)(done by the function below)
---> I want to output the number x in column A corresponding to the
same row as the the FindLastEntry2 number.
Example:
A B
1 23
2 42
1
3 8
4 44
4
------------------------
Function FindLastEntry2(rng)
On Error Resume Next 'ignore errors if input is not a range
nRows = rng.Rows.Count 'count rows in input range
nCols = rng.Columns.Count 'count columns in input range
If nCols = 1 Then 'if one column, search rows
n = nRows
Do Until rng.Cells(n, 1).Value <> 0 Or n = 1
'loop from last row up until non zero found or top of range
n = n - 1
Loop
FindLastEntry2 = rng.Cells(n, 1).Value 'return value found
ElseIf nRows = 1 Then 'if one row search columns
n = nCols
Do Until rng.Cells(1, n).Value <> 0 Or n = 1
n = n - 1
Loop
FindLastEntry2 = rng.Cells(n, 1).Value
Else ' if not a single row or column, return message
FindLastEntry2 = "Please specify a single row or column"
End If
End Function