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

Zacariah171

New Member
Joined
Apr 2, 2019
Messages
25
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.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,630
By your definition this will work:

Code:
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
 
Last edited:

Zacariah171

New Member
Joined
Apr 2, 2019
Messages
25
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,689
Messages
5,597,556
Members
414,155
Latest member
Grainne whiteside

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top