RegExp, find and replace

[Jon von der Heyden]

Hi All

I'm a bit rusty on the ol' regexp and I think I have been trying too hard for too long so I've lost the plot. Any help appreciated...

I have a string in cell E11:

[PLAIN]||=IF($B$8,< ISIN > & " " & IF(< TYPE >="Bond",IF(ISBBERR(BDP(< ISIN > & " Corp",SECURITY_NAME)), "Mtge","Corp"),IF(< ISIN >="Equity","Equity")),FALSE)||[/PLAIN]

Find each sub-string that is like "< ?* >" (excluding spaces!)
Look for the contents (e.g. ISIN) in row 10
Replace the entire sub-string with the cell address that refers to row 11 and the column index where the sub-string was found in row 10

So to give an example... Column labels in row10. ISIN is in column A, TYPE is in G. The result should be:

||=IF($B$8,[B][COLOR="Red"]A11[/COLOR][/B] & " " & IF([B][COLOR="Red"]G11[/COLOR][/B]="Bond",IF(ISBBERR(BDP([B][COLOR="Red"]A11[/COLOR][/B] & " Corp","SECURITY_NAME")), "Mtge","Corp"),IF([B][COLOR="Red"]A11[/COLOR][/B]="Equity","Equity")),FALSE)||

Edit: < ISIN > and such substrings don't actually contain any spaces. I put these in to prevent it from being parsed.

 

[vaskov17]

Not sure if the below code helps but it might. It only fixes the value in cell E11, but it's easy to get it to work on an entire range. There is no string clean up or error checking as well. I know the two loops are bad but......

Sub RegExStuff()
Dim RegEx As New regexp
Dim strTest As String
Dim i As Long
Dim ws As Worksheet
Dim objMatch As Object

Set ws = ActiveSheet

RegEx.Global = False 'find only one match at a time
RegEx.Pattern = "<\w*>" 'any number of capital or lower case letters or numbers enclosed in < >

Do
strTest = ws.Range("E11").Value 'get search string
If RegEx.Test(strTest) Then
Set objMatch = RegEx.Execute(strTest) 'get first match

'loop through headers in row 10
i = 1
Do
'check if header matches search string
If "<" & ws.Cells(10, i).Value & ">" = objMatch(0) Then
'replace value with address
ws.Range("E11").Value = RegEx.Replace(strTest, Replace(ws.Cells(10, i).address, "$", ""))
Exit Do
Else
'to avoid endless loop
If i = 100 Then
Exit Do
Else
i = i + 1
End If
End If
Loop
Else
Exit Do
End If
Loop
End Sub

 

[Jon von der Heyden]

Wonderful, thanks vaskov!

It was the Execute method that you have used and that I overlooked and so desperately needed. So I made few modifications in order to embed it into my project, the most fundamental change being:

If you change Global property to True and then call the Execute method, objMatch with then store an item for each component (since objMatch is a Collection object). You can then optimise further by looping through each item in the collection (using For Each, which is the fasted method of looping through a collection), performing the substitute on each with the cell reference address found.

Thanks again mate. Your help is just what I needed to get myself back on track.