Revise VBA to Parse Out Text for entire sheet

[jlanderson18]

I have a VBA code that will parse out values in a text field in excel that starts with CS or cs followed by 8 digits and add those values in the same row in the empty columns to the right. What I want is for this macro to apply for the entire active sheet, search Column D for values that start with CS or cs followed by 8 digits and parse them out in the same row to the right starting at Column S going right as needed (there could up to 10 CS######## numbers in a single text cell that would need to be parsed out 10 columns to the right from Column S to Column AB)

Thank you my crazy smart Forum members for you expertise!

Here is the code that will parse out for a single active cell:

Sub GetEventIndicators()

Dim rExp As Object, allMatches As Object, match As Object
Dim specialReqs As String, eventIndicator As String
Dim row As Integer, col As Integer

specialReqs = ActiveCell.Value
row = ActiveCell.row
col = ActiveCell.Column

Set rExp = CreateObject("vbscript.regexp")
With rExp
.Global = True
.MultiLine = False
.Pattern = "[cC][sS]\d{8}"
End With

Set allMatches = rExp.Execute(specialReqs)

For Each match In allMatches
eventIndicator = match.Value
'Debug.Print "Event Indicator: " & eventIndicator
col = col + 1
Cells(row, col).Value = UCase(eventIndicator)
Next

End Sub

[MarvinP]

Hi jlanderson,

We can do this problem a lot easier if you would supply a sample workbook showing the problem, with a few rows of data, and what you expect the answer to be. Also, could you edit your original post and select the code and click on the "#" icon that will put code tags around the code, so it is easier to read.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[jlanderson18]

I believe I have an attachment.

[jlanderson18]

Thanks Marvin - from one Seattlite to another - attachment is attached.

[MarvinP]

OK jlanderson,

I put some minor changes to what you already had. Clear your yellow area on the second sheet and run the macro.

Please Login or Register  to view this content.

I hope my "hack" will give you enough example to do whatever else you need. The person who wrote this was pretty nerdy, using a CreateObject("vbscript.regexp").

EventIndicators Answer.xlsm

[jlanderson18]

Like a charm!! Thanks Marvin. Can't wait to tell someone what a nerd they are!