Answered by:
VB: .pattern problem, Need a UDF to find a MAX Number, within a Formula
Question

1. where is a chart? for the meanings behind: aRegExp.Pattern = " symbols. (i was on a regexp forum, but think question is bigger/ other xl)
examples seen are:
string pattern = @"/(\d+?)/rs/"
Regexp.Pattern="[09][09]*"
finding some info at: https://en.wikibooks.org/wiki/Visual_Basic/Regular_Expressions
2. i have a formula i need to find the max number within a formula, for numbers after a > or < sign (and if matters: followed by a comma)
=IF(A1>7,IF(A1>9,"Y","X"),IF(A1>3.5,"A",IF(A1>2,"B",IF(A1>.50,"C","D")))) i need to find the: MAX(7,9,3.5,2,.50) with end result of: 9
note: the number will follow either a less than, or greater than sign.
i think a UDF would work fine for this, and i found a couple of examples that are supposed to work on the same string, and i cannot see how to change them (to discern pattern symbols etc) to work with the above example (> or < signs); the first one does not seem to use a pattern: (thanks)
Function maxSTR(rng As String) As Double 'prob: command in cell not go to correct case, find reset note / what wrong (make istrue fix note)
Dim splt() As String: Dim i&
splt = split(rng)
For i = LBound(splt) To UBound(splt)
If IsNumeric(splt(i)) Then
If splt(i) > maxSTR Then
maxSTR = splt(i)
End If
End If
Next i
'test YES for STRING below, need for: > or <
'[[ E:\DATA\SQL\SY0\ , 19198 ],[ E:\ , 18872 ],[ E:\DATA\SQL\ST0\ , 26211 ],[ E:\DATA\SQL\ST1\ , 26211 ],[ E:\DATA\SQL\SD0\ , 9861 ],[ E:\DATA\SQL\SD1\ , 11220 ],[ E:\DATA\SQL\SL0\ , 3377 ],[ E:\DATA\SQL\SL1\ , 1707 ],[ E:\DATA\SQL_Support\SS0\ , 14375 ],[ E:\DATA\SQL_Support\SS1\ , 30711 ]]
End FunctionThe next example is supposed to work on the above, same string but i could not get it to work at all. this example has the .pattern item.
Function maxSTR1(S As String) As Double 'max string NO
Dim RE As Object, MC As Object, M As Object, D As Double
Set RE = CreateObject("vbscript.regexp")
With RE 'eg's: aRegExp.Pattern = "\.gif[\w+ ?= ?\w+]*>" at: http://www.programmingexcel.info/Regular_expressions_for_replacements_in_Excel_.html
.Global = True
.pattern = ">b[09]*,.?[09]+>b" 'pattern prob ? goog: aRegExp.Pattern = "
'.pattern = "\b[09]*\.?[09]+\b" 'orig
If RE.test(S) = True Then
For Each M In MC
D = IIf(D > CDbl(M), D, CDbl(M))
Next M
End If
End With
'test NO for below, need for: > or <
'[[ E:\DATA\SQL\SY0\ , 19198 ],[ E:\ , 18872 ],[ E:\DATA\SQL\ST0\ , 26211 ],[ E:\DATA\SQL\ST1\ , 26211 ],[ E:\DATA\SQL\SD0\ , 9861 ],[ E:\DATA\SQL\SD1\ , 11220 ],[ E:\DATA\SQL\SL0\ , 3377 ],[ E:\DATA\SQL\SL1\ , 1707 ],[ E:\DATA\SQL_Support\SS0\ , 14375 ],[ E:\DATA\SQL_Support\SS1\ , 30711 ]]
End Function
 Edited by Davexx Friday, May 26, 2017 3:01 AM
Answers

NonRegex Version
Function maxSTR(rng As Range) As Double Dim splt As Variant Dim i As Long Dim c As Long splt = Split(rng.Formula, ",") For i = LBound(splt) To UBound(splt) c = InStr(1, splt(i), ">") If c = 0 Then c = InStr(1, splt(i), "<") End If If c > 0 Then If Mid(splt(i), c + 1, 100) > maxSTR Then maxSTR = Mid(splt(i), c + 1, 100) End If End If Next i End Function
Regex Version
Function maxSTRr(rng As Range) As Double Dim RE As Object, MC As Variant, M As Object, D As Double Set RE = CreateObject("vbscript.regexp") S = rng.Formula With RE .Global = True .Pattern = "(><)([09]+)," If RE.test(S) = True Then Set MC = RE.Execute(S) For Each M In MC D = IIf(D > CDbl(M.SubMatches(1)), D, CDbl(M.SubMatches(1))) Next M End If End With maxSTRr = D End Function
Best Regards,
Asadulla Javed,
Jadavpore & Asansol Marked as answer by Davexx Saturday, May 27, 2017 3:23 PM
All replies

NonRegex Version
Function maxSTR(rng As Range) As Double Dim splt As Variant Dim i As Long Dim c As Long splt = Split(rng.Formula, ",") For i = LBound(splt) To UBound(splt) c = InStr(1, splt(i), ">") If c = 0 Then c = InStr(1, splt(i), "<") End If If c > 0 Then If Mid(splt(i), c + 1, 100) > maxSTR Then maxSTR = Mid(splt(i), c + 1, 100) End If End If Next i End Function
Regex Version
Function maxSTRr(rng As Range) As Double Dim RE As Object, MC As Variant, M As Object, D As Double Set RE = CreateObject("vbscript.regexp") S = rng.Formula With RE .Global = True .Pattern = "(><)([09]+)," If RE.test(S) = True Then Set MC = RE.Execute(S) For Each M In MC D = IIf(D > CDbl(M.SubMatches(1)), D, CDbl(M.SubMatches(1))) Next M End If End With maxSTRr = D End Function
Best Regards,
Asadulla Javed,
Jadavpore & Asansol Marked as answer by Davexx Saturday, May 27, 2017 3:23 PM

update: seems i may have finally found a ms site describing regex. with goog: excel version vbscript.regexp site:microsoft.com
hard to find ?!! not sure if can see an excel version required..
https://msdn.microsoft.com/enus/library/ms974570.aspx
==========
Thank you !! that worked great for the non regex version. have to ask if there is a minumum excel version required for the 2nd / regex version ? (i have not been able to get any sample of regex to work so far).
(For the REGEX example, a compile is asking to define: S) i am trying to make those changes, but not sure i will get that.
FOR THIS ITEM it looks like the problem might be: having both signs in 1 formula for the maxSTR udf, for both: > and <
i have an example for adding an Array choices to a VB. it will take me awhile to check that out though. (am slow with vb).
for the first example (non regex) on at least 1 line i got an value error in the cell. exact example of formula testing has:
=IF(A9>60,"9/1/2000",IF(A9>27,"1/15/2001",IF(OR(A9<12,A9>24),"2/1/2006",IF(A9<14,"6/1/2012",IF(A9<11.5,"8/1/2008",IF(OR(A9<16,1),"6/1/2013","1/1/2014"))))))
maxSTR = Mid(splt(i), c + 1, 100) 'max 1900 worked, error:
'in sheet 1 line: value error, calc'd line got error here this line: TYPE MISMATCH
==========
you wouldn't believe the amount of hours i spend on trying to research this stuff.. spent 10 or 12 hours on this for just yesterday. i saw a site explaining regex, looked like its own program language  had to stop there.
and links: https://en.wikibooks.org/wiki/Visual_Basic/Regular_Expressions
thanks.
==========
for any who have a problem with the correct case setting showing up within a cell, for a udf, the following seems to work:
'FIX / RESET FUNCTION CASE: capsxx() casexx() CAPSXX() CASEXX()
'rename function name eg: sameNAMEx to add "x", COMPILE VB (debug, compile; ignore error), REM x, COMPILE, SHEET: in one cell rename udf function with correct case & hit enter, done. (might think there could be some script for that).
==========
for others: having a formula that depends on a main number or price in another column.. if that number changes by a factor of eg: 2, 5, 10 etc other columns become obsolete / the whole line will be in error. this test will allow you to see that. example test:
=IF((maxSTR(A9)>0)*(maxSTR(A9)*1.5<M9),"Error",0)
==========
having a hyperlink in header, to jump to the first instance of this example should work: (pending, trying to clear up errrors), cntrlshiftenter array formula, in cell: X3 (row where this resides, column you want cursor to go), adjust columns & rows.
=HYPERLINK(IF(AND(ISNA(INDEX(ROW($AK$861:$AK$2112)ROW($AK$861),
MATCH(TRUE,EXACT($AK$861:$AK$2112,"Error"),0)))),"","#"&CELL("address",OFFSET(INDIRECT(SUBSTITUTE(SUBSTITUTE(CELL("address",$X3),"$",""),ROW(),"")&INDEX(ROW($AK$861:$AK$2112),
MATCH(TRUE,EXACT($AK$861:$AK$2112,"Error"),0))),0,0))),SUMPRODUCT((EXACT($AK$861:$AK$2112,"Error"))))
 Edited by Davexx Saturday, May 27, 2017 5:19 PM

hi, i had a chance to look the nonregex version over more. i have limited vb skills & was not able to get the following to work for a couple of small problems. thanks.
please pardon i did not think to include all versions of formula using (i do a different formula for each line). with all the work doing on researching this i overlooked that / wo thinking about it, i assumed "OR" was enough.
an example of a formula with both ">" OR "<". i was not able to get all versions of the formula included, pardon  each line a different formula is entered. (note: max numbers seen might be 300, for the regex version, to cause a .pattern problem? i see on some web pages how involved that can be; is there an excel version requirement?).
=IF(A9>10,IF(A9>12,"Z","Y"),IF(A9<2,"A",IF(A9<4,"B","C"))) 'MAX 12 for both > or < signs, gets a Value Error
'1. is there a way to inlcude a: "Greater than zero" number of > or < in udf? so i do not need this full expression:
IF((maxSTR(AO647)>0)*(maxSTR(AO647)*3<FU647), (else a false positive without IF > 0 portion)
'2. how to modify if i have both ">" & "<" in the same formula? i could not get an array to work.
Function maxSTR(rng As RANGE) As Double 'YES NonRegex Version (Asadulla Javed 170527)
Dim splt As Variant, i As Long, c As Long, v As Variant
splt = split(rng.Formula, ",")v = Array(">", "<") ', "=") 'eg: Set c = r.find(what:=v(i)
For i = LBound(splt) To UBound(splt)
'c = InStr(1, splt(i), v) 'no
c = InStr(1, splt(i), ">") 'orig
'If c = 0 Then c = InStr(1, splt(i), v) 'no
If c = 0 Then c = InStr(1, splt(i), "<") 'yes separate "=" not help, try array
If c > 0 Then
If Mid(splt(i), c + 1, 100) > maxSTR Then
maxSTR = Mid(splt(i), c + 1, 100) 'err: type mismatch: have eg's with both: > & <
End If
End If
Next i
End Function
 Edited by Davexx Sunday, May 28, 2017 3:44 AM

You can use below.
Visit http://www.regularexpressions.info/
Above contains good reference.
Function maxSTRr(rng As Range) As Double Dim RE As Object, MC As Variant, M As Object, D As Double Dim S As String Set RE = CreateObject("vbscript.regexp") S = rng.Formula With RE .Global = True .Pattern = "(><)([09]+)." If RE.test(S) = True Then Set MC = RE.Execute(S) For Each M In MC D = IIf(D > CDbl(M.SubMatches(1)), D, CDbl(M.SubMatches(1))) Next M End If End With maxSTRr = D End Function
Best Regards,
Asadulla Javed,
Jadavpore & Asansol 
hi, thanks for the link, and the reply. i still have not been able to the regex version to work (maybe an excel version problem), but have had some success with the NON regex version, that i need some help with.
can i get help with changing Non Regex version, to include something more like an array? for testing both items simultaneously, in a formula?
i have a couple of other items / tricks i used to get Non Rex to work. part of it is using another udf called ISTRUE (udf for isnumber & greater than 0), and using 2 columns. (temporary get past error in main column), with 2 items:
=IF(ISERROR(maxSTR(A9)),0,maxSTR(A9)) '2 columns to turn an error into 0.
IF((ISTRUE(A9))*((A9)<m9),"maxERR","") 'i can include a copy of istrue in my top post later, just a number>0 test
problem 1. original formula i need to work with. would like to reduce formula, to remove >0 1st half.. if can include that test right in the udf.
=IF((maxSTR(A9)>0)*(maxSTR(A9)*3<M9),"maxERR","")
is there a way to use (or change the udf) so i can get rid of the first variable for > 0, and incude that test inside the udf itself? want to remove from formula: (maxSTR(A9)>0)
i think i saw an example for adding a boolean TRUE FALSE to a UDF. (i will put that in next post).
problem 2. THE Preferred version using is the Non Regex Version. can you help change this udf do include both ">" & "<" in the same test formula eg:
=IF(M9>17,IF(M9>20,"Z","X"),IF(M9<9,"A",IF(M9<10,"B",IF(M9>13,"C","D"))))
==========
what working with so far, need to fix: 1. make udf not have an output if result is zero. 2 add array? for both ">" & "<" items to be tested simultaneously.
Function maxSTRB(rng As RANGE) As Double 'almost, NonRegex Version (Asadulla Javed 170527)
Dim splt As Variant, i As Long, c As Long, v As Variant
splt = split(rng.Formula, ",")
v = Array(">", "<") ''<< array not working yet, is there a way to include something like this ??
For i = LBound(splt) To UBound(splt)
'c = InStr(1, splt(i), v) '<< no, array version not working
c = InStr(1, splt(i), ">") 'orig
'If c = 0 Then c = InStr(1, splt(i), v) '<< no, array version not working
If c = 0 Then c = InStr(1, splt(i), "<") 'yes, but need both > <
'If c = 0 Then GoTo GETOUT: 'see: yneg, does not help get rid of extra variable in formula?
If c > 0 Then
If Mid(splt(i), c + 1, 100) > maxSTR Then 'somehow got max out of these 2 lines?
maxSTR = Mid(splt(i), c + 1, 100)
End If
End If
Next i
'GETOUT:
End Function
 Edited by Davexx Monday, May 29, 2017 7:27 PM

the TRUE FALSE example i have happens to be with the "ISTRUE" udf i mentioned above. am only guessing that this may help get rid of false positive results.
Function isTRUE(ByRef X As RANGE) As Boolean 'as: IF(ISTRUE(A1),1,0)
On Error Resume Next 'for combine: AND(if(isnumber(a9),a9>0)
isTRUE = False
If IsNumeric(X.Value) And X.Value <> vbNullString And X.Value > 0 Then isTRUE = True
If Not IsNumeric(X.Value) Then isTRUE = False
End Function 'istrue() ISTRUE()
 Edited by Davexx Monday, May 29, 2017 7:24 PM


update note: to summarize, the udf seems to be working now, and if there is somethin i need to do to close this thread, let me know. i added the explanations below to try to show how it might be working now/ i have been working on many areas for my workbook. (not meant to continue the question). thanks much for your help.
pardon: somehow hit post key while i am writing this: wait till finished please, working now. (Rcommend 1st tab key not be the SUBMIT button.
hi Mr Javed, pardon late reply, sometimes up nights  down for a period of time. i think i have problem resolved. i added some things for correct errors (as below). but am not sure of what exactly makes both > and < characters work now. (i don't see how that is accomplished, since had an error, i was giving my isTRUE udf eg thought aspects for: TRUE  FALSE would help).
one thing i was trying to do is shorten up my formula, to get rid of the first variable for: > 0. i am not sure if that is possible, and / or for using true false eg above. maybe not possible? but i wanted to change the formula (from / to):
=IF((maxSTR(A9)>0)*(maxSTR(A9)*3<M9),"maxERR","zzz") 'double variable test
=IF(maxSTR(A9)*3<M9,"maxERR","zzz") 'single variable test, if can put > 0 aspect inside the udf. if not already part of that ?? :) checking now.
thought there was a problem if i was using both variables: ">" and "<" in the same formula, to get an error. this does not seem to be happening now. Answer for that is: NO, it is still a problem. the following gets a false positive / "maxERR", for: ="C". i am slow at vb, but i can look at that more.
i use a work around for name errors i receive for udf's (name resolution problem). a copy of that is below. not sure why it works now, but if i made some correction :) please pardon taking up so much time. i had worked many hours on this though.
my "work around"? for some name convention errors i have been receiving, where the name for a UDF would not resolve back the sheet & I would get a NAME ERROR (if i am assessing it correctly).
the correction for that is (for a call & supporting vb):
selection.EntireRow.Calculate: call fixROW: fixROW: fixROW 'example of a call, just calc or part of cut copy..
Sub xfixROWout() 'clear name errors with .formula = .formula
Dim rngErrors As RANGE, cellError As RANGE, S As RANGE, c As RANGE
Set S = selection.EntireRow.Cells
On Error Resume Next 'If no error cells then next code line errors
Set rngErrors = ActiveSheet.Rows(selection.row).SpecialCells(xlCellTypeFormulas, xlErrors).Cells 'works for rows
On Error GoTo 0 'Resume error trapping ASAP.
If Not rngErrors Is Nothing Then 'yes works here
For Each c In S 'gd
If IsError(c) And c.HasArray = False Then 'And Len(c.Formula) <= 256 Then 'LEN not seem to work here
c.Formula = c.Formula 'yes
End If
Next
Else
'MsgBox "yep1" & Space(10), vbQuestion
End If
application.EnableEvents = True 'EVENTS (DON'T FORGET TO TURN A1 BACK ON: a1="." safety, all macs off)
End Sub
==========
i was not getting a name error for this (i forget error type), but i had to use a separate column to isolate because i could not realize the IF(NOT(ISERROR() for syntax, to put ahead of this vb udf to use it in the same cell / column. i was using / have removed this work column:
IF(ISERROR(maxSTR(A9)),0,maxSTR(A9)) 'now use in main column:
=IF((maxSTR(A9)>0)*(maxSTR(A9)*3<M9),"maxERR","zzz")
if i did anything in your vb i don't realize.. it is:
Function maxSTR(rng As RANGE) As Double 'YES NonRegex Version (Asadulla Javed 170527)
On Error GoTo 0: On Error GoTo GETOUT: 'clears error trapping, exit
Dim splt As Variant, i As Long, c As Long ', v As Variant
splt = split(rng.Formula, ",")
'v = Array(">", "<") ', "=") 'array not working yet, eg: Set c = r.find(what:=v(i)
For i = LBound(splt) To UBound(splt)
'c = InStr(1, splt(i), v) 'no
c = InStr(1, splt(i), ">") 'orig
'If c = 0 Then c = InStr(1, splt(i), v) 'no
If c = 0 Then c = InStr(1, splt(i), "<") 'yes, but need both > <
'If c = 0 Then c = InStr(1, splt(i), "=") '(no effect on 1 variable eq with "=" sign) 'If c = 0 Then Exit Sub 'not allowed in a function or property
'> If c = 0 Then GoTo GETOUT: 'does not help get rid of extra variable in formula? for: =IF(AND(ISNUMBER(AG9),AG9>0,AG9*3<FT9),"maxERR","")
If c > 0 Then '
If Mid(splt(i), c + 1, 100) > maxSTR Then 'gets max out of these 2 lines?
maxSTR = Mid(splt(i), c + 1, 100) 'max 1900 worked, err: type mismatch: have eg's with both: > & <, turned goto off on (compile gd), off err during DL1x
End If
End If
Next i
'think now working for this:
'=IF((maxSTR(A9)>0)*(maxSTR(A9)*3<M9),"maxERR","zz")
GETOUT:
End Functionthe proof i just performed is below. Z9 value is: 14. formula is: =maxSTR(A9), A9 egs are: (for results):
=IF(Z9>13,IF(Z9>45,"Y","X"),IF(OR(Z9<7.5,0),"A",IF(OR(Z9<9,1),"B","C"))) X 45 for: >
=IF(Z9>13,IF(Z9<45,"Y","X"),IF(OR(Z9<7.5,0),"A",IF(OR(Z9<9,1),"B","C"))) Y 45 for: <
=IF(Z9>15,IF(Z9<45,"Y","X"),IF(OR(Z9<7.5,0),"A",IF(OR(Z9>60,1),"B","C"))) B 60 for: > OR(x,1) involved
=IF(Z9>15,IF(Z9<45,"Y","X"),IF(OR(Z9<7.5,0),"A",IF(OR(Z9<60,1),"B","C"))) B 60 for: <
 Edited by Davexx Friday, June 2, 2017 10:36 PM