Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to find a row since I found a value in a column? see example
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 ----------------------------- Nick |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to find a row since I found a value in a column? see example
I forgot to mention that sometimes there is more than one time the same
value in column B |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find a string within a column | Excel Discussion (Misc queries) | |||
Macro to find text string in a column and paste data in another | Excel Discussion (Misc queries) | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions | |||
I need to find the Average from Column A - but Reference Column B | Excel Worksheet Functions |