VBA Search and Replace with wildcard?

[Jadelyn]

Hi all,

I am working on a cleanup macro to take the raw report our HRIS spits out (which is incredibly un-user-friendly) and give back a usable table. One part of it is going through the column of employee schedules and changing the codes it starts with to equivalent numerical values - it starts out as "CORP40" for 40 hours/week, "CORP20" for 20 hours/week, etc.

Currently I just have a block of code that runs a find-and-replace for each value that the report currently contains, like so:

Cells.Replace What:="CORP40", Replacement:="40", LookAt:=xlPart, _        
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="CORP16", Replacement:="16", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace What:="CORP32", Replacement:="32", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

And so on. This is the code that was generated when I originally recorded the macro six months ago, but I'm trying to clean things up so it's not so bulky and slow and ugly now that I've started learning how to write VBA code manually.

The problem is twofold - one, that the total list of possible schedule codes the system uses is very long and so this only contains the codes currently in use, meaning if someone's schedule changes to one that isn't currently in use I have to remember to manually add a new find and replace line to fix that specific schedule code. And two, that this section still kind of bogs down the whole thing when I run it because it's doing this find and replace a few dozen times in total.

My question is, is there a way to use a single find and replace command to search for "CORP*" and have the output be just whatever was in the "*" part of the string? Such that it would see "CORP40" and return "40", "CORP20" and return "20", etc. without separating out each possible search term as its own find-and-replace command? I found a couple of maybe-relevant solutions on other sites using regexp but I'm not familiar enough with how that works to try taking solutions for someone else's needs and tweaking them to suit what I'm trying to do.

 

[V_Malkoti]

Try this.

Cells.Replace What:="CORP", Replacement:="", LookAt:=xlPart, _        
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

This code should remove "CORP" string and leave the number part intact. Which means this one single statement should replace all.

 

[Joe4]

My question is, is there a way to use a single find and replace command to search for "CORP*" and have the output be just whatever was in the "*" part of the string? Such that it would see "CORP40" and return "40", "CORP20" and return "20", etc. without separating out each possible search term as its own find-and-replace command? I found a couple of maybe-relevant solutions on other sites using regexp but I'm not familiar enough with how that works to try taking solutions for someone else's needs and tweaking them to suit what I'm trying to do.

Absolutely. As matter as fact, you are already halfway there.
Note this argument:

LookAt:=xlPart

This tells it in only has to find a partial match (and not match the whole entire cell entry).
So, just tell it to replace "CORP" with nothing, i.e.

Cells.Replace What:="CORP", Replacement:="", LookAt:=xlPart, _        
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

 

[Jadelyn]

...and this is why I'm trying to learn to write code rather than record it. I had no idea what the LookAt value actually did. Thank you both so much!

 

[Joe4]

You are welcome!

...and this is why I'm trying to learn to write code rather than record it.

Actually, I find it quite useful to use both. The Macro Recorder can be a great tool to get snippets of code. You can then check them out and edit them (and research where necessary).

 

[Jadelyn]

Good point - that's how I've gotten my start in learning this stuff, in fact, by recording single actions to add to existing macros and working from there.