Excel VBA

[afeldsh]

Hello, I am looking for some help with excel VBA. This forum has been very helpful to me in the past.

I have a bill of materials that represents structure with numbers. They can repeat anywhere on the structure. I would like select any one of those numbers in a cell and run VBA to tell me which row above this row is the next lower number. Any help is appreciated.

0
1
2
2
2
2
3
3
3
3
2
1
1
1

 

[afeldsh]

Example file attached

 

[Joe4]

I don't understand your question (and perhaps others do not as well, which may be why you have not gotten any replies).
Can you walk us through an actual example, using the sample you posted in your last post?

 

[afeldsh]

I don't understand your question (and perhaps others do not as well, which may be why you have not gotten any replies).
Can you walk us through an actual example, using the sample you posted in your last post?

Using the picture I provided above:

1) Example 1: Lets say I select cell A25 which has a value of 3 in that cell, I need a macro to tell me which row has the next lower number in column A above the selected row. In this case the response should be row 14 - which has the next lower value of 2 above this row.

2) Example 2: Lets say I select cell A44 which has a value of 1 in that cell, I need a macro to tell me which row has the next lower number in column A above the selected row. In this case the response should be row 2 - which has the next lower value of 0 above this row.

I want to select any cell in column A and have the macro do the same as in the examples above

Hope it makes more sense on what I am after. Thank you

 

[Joe4]

Try this:

Sub FindLowerValue()

    Dim r As Long
    Dim ar As Long

'   Check to see that you are in column A
    If ActiveCell.Column <> 1 Then
        MsgBox "You have not selected a cell in column A!", vbOKOnly, "TRY AGAIN!"
        Exit Sub
    End If
    
'   Get row number of active cell
    ar = ActiveCell.Row
    
'   Loop through all rows backwards
    For r = ar To 2 Step -1
'       Check value
        If Cells(r, 1) < Cells(ar, 1) Then
            MsgBox "Row number " & r & " has lower value", vbOKOnly, "ROW NUMBER FOUND!"
            Exit Sub
        End If
    Next r

End Sub

 

[afeldsh]

Try this:

Sub FindLowerValue()

    Dim r As Long
    Dim ar As Long

'   Check to see that you are in column A
    If ActiveCell.Column <> 1 Then
        MsgBox "You have not selected a cell in column A!", vbOKOnly, "TRY AGAIN!"
        Exit Sub
    End If
   
'   Get row number of active cell
    ar = ActiveCell.Row
   
'   Loop through all rows backwards
    For r = ar To 2 Step -1
'       Check value
        If Cells(r, 1) < Cells(ar, 1) Then
            MsgBox "Row number " & r & " has lower value", vbOKOnly, "ROW NUMBER FOUND!"
            Exit Sub
        End If
    Next r

End Sub

Exactly what I was looking for. Thanks so much for your help! Have an awesome day!

 

[Joe4]

You are welcome.
Glad I was able to help!