Find a cell that matches a pattern and return that cell's value with an incremented numerical value to the Active Cell

[Zacariah171]

I am trying to search for the first cell above my active cell (just in the same column) that contains the words BOX or SPARE. The words BOX and SPARE will also be followed by an integer value that's up to 3 digits long (e.g., SPARE 22 or BOX 81). I'm trying to find the first cell that contains that pattern and return that value to the active cell with the number incremented by 1. For instance, if my active cell is A40, I'd like to check the cells above it in the same column for the first match. So, if A39 doesn't match, then check A38. If A38 doesn't match, then check A37. If A37 matches and is SPARE 12, then I'd like A40 to equal SPARE 13. If A37 was BOX 15, then A40 would be BOX 16.

So far, I've been able to find the cell I'm looking for with the following code but 1) I'm not sure how to save the location of the original active cell (in the case above, A40) and 2) I'm just not sure how to return the value with the incremented numerical value to the original active cell (A40).

Dim regex As Object
Dim PrevEquip As String


Set regex = CreateObject("VBScript.RegExp")


With regex
.Pattern = "BOX|SPARE"
End With

Do Until regex.Test(ActiveCell) = True
Debug.Print regex.Test(ActiveCell)
If regex.Test(ActiveCell) = False Then
ActiveCell.Offset(-1, 0).Select
End If
Loop
PrevEquip = ActiveCell.Value




Am I going in the right direction with the code above? Should I use a Find method instead? I hope I've provided enough information but please let me know if you need more info about what I'm trying to accomplish. Any help is greatly appreciated.

 

[mrshl9898]

By your definition this will work:

Sub findabove()

Dim colnum As Long
Dim rownum As Long
Dim foundval As Long


rownum = ActiveCell.Row
colnum = ActiveCell.Column
foundval = 0


Do Until foundval = 1 Or rownum = 1
rownum = rownum - 1
    If InStr(UCase(Cells(rownum, colnum)), "BOX") <> 0 Then
    myval = CInt(Mid(Cells(rownum, colnum), InStr(Cells(rownum, colnum), " "), 4)) + 1
    ActiveCell.Value = "BOX " & myval
    foundval = 1
    ElseIf InStr(UCase(Cells(rownum, colnum)), "SPARE") <> 0 Then
    myval = CInt(Mid(Cells(rownum, colnum), InStr(Cells(rownum, colnum), " "), 4)) + 1
    ActiveCell.Value = "SPARE " & myval
    foundval = 1
    End If
Loop


End Sub

 

[Zacariah171]

That worked Perfectly! Thank you so much! I had been working on that all day and just couldn't get it right. I'll have to take a closer look at what you did there and educate myself on what you used. Thank you again!