Excel Macro - Return looks up a column to find lower number.

Smilesxl
Smilesxl used Ask the Experts™
on
Spreadsheet has "Level"  in column A, "Part #" in column B, in column C is "Next Level Part #" and is what I need to populate.

Usually there is a level 1, that is the highest and it will have in the next rows, all of it's level 2 parts and some level 3's and 4's.   Essentially I need a column that shows the next level up the part number in that row.  So if it is a level 4 part, I need the level 3 part to be populated in the "Next Level Part #" row.   So essentially I need it to look at the level of the row it is in, then look above to the next lower number, and return the "Part #" of that row.    

Hope someone can help.  I used to do this stuff, but have not even looked at Excel in 5 years.  (I know luck me, right).   But I have forgotten most of what I used to know.  It is not like riding a bike.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
See file with a macro function

Function NextLevelPartNumber(Level As Range)
    Application.Volatile
    Dim rw As Long, Found As Integer
    If Level.Value = 1 Then
        NextLevelPartNumber = "Top part"
    Else
        rw = Level.Row
        Found = 0
        Do
            rw = rw - 1
            If Cells(rw, Level.Column) = Level.Value - 1 Then
                Found = 1
                NextLevelPartNumber = Cells(rw, Level.Column + 1)
            End If
        Loop Until Found = 1 Or rw = 1
    End If
End Function

Open in new window

NextLevelPartNumber.xlsm

Author

Commented:
I am not sure how to call a custom function?  I type in =NextLevelPartNumber (range?).  Never mind, I got it, thanks.

Author

Commented:
Thank you so much!  Great solution.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial