VBA code that will apply to certain cells based on their value

batman1 · #16 02-06-2025, 07:24 PM

Quote:

Originally Posted by gmaxey

'**********************************************REG EX PATTERN QUESTIONS***************************************** *****************
'1. Could we use the system separator in the pattern ?? 'oRegEx.Pattern = "^((?:\d*" & strSep & ")?\d+)(?:|\-)(US|UK|AP|BR|L).*$" _
'2. In original, the "((?:\d*\.)?\d+)"
'I understand that second "?" means the preceeing parenthetical elment (?:\d*\.) does not have to be found. _
That the \d* represents 0 or more numbers, and the \. is a literal period (or in our case decimal separator). _
But what is the purpose of "?:"
'As best I can tell from testing. The pattern can be rewritten either:
'oRegEx.Pattern = "^((\d*\.)?\d+)(?:|\-)(US|UK|AP|BR|L).*$"
'Where those two charcters just aren't used ...
'or ... use a an OR |
'oRegEx.Pattern = "^(\d*\.\d+|\d+)(?:|\-)(US|UK|AP|BR|L).*$"
'3. Similiarliy, the "(?:|\-)".
'I have deduced that signifies the "-" separating the number and region is not required.
'But, what does the "?:" and "" signify?
'As best I can tell from testing. The pattern can be rewritten:
'oRegEx.Pattern = "^((\d*\.)?\d+)(-*)(US|UK|AP|BR|L).*$"
'**********************************************END QUESTIONS***************************************** *****************

1. If OP uses dot in the data: 0.75-UK1234, 0.5BR, 1.25-AP, … then there will always be DOT in this data. But strSep in OP's system is DOT and on my system it is COMMA. For this it has to be DOT and not strSep.

2. Yes, except that \. is a literal period, not a decimal separator, because someone has decimal separator = DOT and I have decimal separator = COMMA.

Read about Backreferences, capturing group, non-capturing group.
a. (\w+) --> capturing group --> \1, \2, … --> $1, $2, …
you have to remember the group somewhere so that you can refer to it later (\1, $1)

b. (?:\w+) --> non-capturing group
You don't have to remember the group because you won't need it later. You can use capturing group but why.

3. (|\-) = "" OR "-"

In regEx some characters do not mean themselves, they have a different meaning. For example "." does not mean DOT but any character. If we want to use DOT we have to precede with the "" character
"." --> any character
"\." --> DOT.
I don't remember if "-" is a special character so I will use "\-". I guess you can use "-". But "\-" can be too.
"a" --> "a"
"\a" --> also "a".
Naturally:
There are many solutions to the same mathematical problem.
There are many roads that lead to Rome.
There are many patterns that mean the same thing.

gmaxey · #17 02-07-2025, 01:16 AM

Batman1

Thanks for this.

gmaxey · #18 02-07-2025, 05:52 PM

Cross posted full file here General Newbie Questions and Request for feedback in hope of getting other ideas.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Formula or VBA code to format certain cells based on the cell value	Seeker2025	Excel	1	02-03-2025 01:14 AM
Apply a formula based on whether a cell is blank or has a date	NLJ	Excel	4	09-17-2021 06:02 AM
VBA Code apply draft	lodi123	Word VBA	1	02-21-2017 04:55 AM
Conditional formating all cells in an array based on adjacent cells	deejay	Excel	6	12-20-2016 12:00 PM
Is it possible to have a style apply two formats based upon text?	DMcCollum	Word	3	05-02-2015 06:29 PM