Excel Macro Find and Replace Functions within Cell Formulae

[theandrewgor]

I have found something rather annoying with excel. Running index match to file in a table. Then the table populates lots of formulas. At the moment it has statements like:
IF(ISBLANK(B93),0 etc. but I need to expand its ability to account for every form of blank/0/"". I need to do a find and replace that says for all cells on worksheet 'main', sometimes multiple times in some cells.
sample of what I would replace with if(OR($B93=0,0<COUNTBLANK($B93)))

If isblank(*2-5char*) replace with or(*2-5char*=0,0<countblank(2-5char*))

I would think the solution would be:
Find first instance of isblank, find first following), store from end of isblank to ), write stored into formula listed above....

Thoughts? A good way to do this and loop through all instances of this?

Ps. Here's one of the reasons I would possibly need it a couple times in a cell.
=IF(OR(ISBLANK(B37),ISBLANK(B38),ISBLANK(B39)),"N",IF($B$36="Y","R"&C38&C37,C39&C38&C37))


Thank you for your assistance.

[theandrewgor]

I am thinking I would need to use a RegEx statement....

[mdmackillop]

Can you post a sample file showing data and desired result. Use Go Advanced/Manage Attachments.
If you're looking to use such wildcards in your cells, I doubt that is going to work.

[theandrewgor]

Can you post a sample file showing data and desired result. Use Go Advanced/Manage Attachments.
If you're looking to use such wildcards in your cells, I doubt that is going to work.

19120
Here is a junk attachment that shows how different text is treated and how the countblank insert helps.

and here's an imperfect snippet of what the replace would do if it were a formula, but that wouldnt work for finding all instances in a worksheet and multiple per cell. As far as i know.
=SUBSTITUTE(E1,"ISBLANK","or("&MID(E1,FIND("ISBLANK",E1)+7,FIND(")",E1)-FIND("ISBLANK",E1)-6)&"=0,0<countblank"&MID(E1,FIND("ISBLANK",E1)+7,FIND(")",E1)-FIND("ISBLANK",E1)-6)&")",1)


Thank you for any feedback you may have. :)

[theandrewgor]

Here is what a cell would look like before formatting.
Original text:
IF(OR(AND(ISBLANK(B34),VALUE(C20)>2001,OR(PortfolioCode="CPIC",PortfolioCode="USAAREMRK")),AND(ISBLANK(B34),VALUE(C20)>2001,VALUE($B$268&$B$269&$B$270)<$C$264)),37,IF(ISBLANK(B34),1,VLOOKUP(B34,RoofCoverCode,2,0)))



If it helps: Formula used to correct first instance (would want all instances corrected):

=SUBSTITUTE(SUBSTITUTE($E$1,MID($E$1,FIND("ISBLANK",$E$1)+7,FIND(")",$E$1)-FIND("ISBLANK",$E$1)-6),")",1),"ISBLANK","or("&MID($E$1,FIND("ISBLANK",$E$1)+7,FIND(")",$E$1)-FIND("ISBLANK",$E$1)-6)&"=0,0<countblank"&MID($E$1,FIND("ISBLANK",$E$1)+7,FIND(")",$E$1)-FIND("ISBLANK",$E$1)-6),1)





Corrected first instance using my formula.



IF(OR(AND(or((B34)=0,0<countblank(B34)),VALUE(C20)>2001,OR(PortfolioCode="CPIC",PortfolioCode="USAAREMRK")),AND(ISBLANK(B34),VALUE(C20)>2001,VALUE($B$268&$B$269&$B$270)<$C$264)),37,IF(ISBLANK(B34),1,VLOOKUP(B34,RoofCoverCode,2,0)))

[mdmackillop]

I think this is giving the correct substitution, but maybe some fine tuning is required



Sub test()
Dim arr(), arrF()
Dim t As String, f As String
Dim x, addr
Dim cel As Range

For Each cel In Selection
t = cel.Formula
x = Split(t, "ISBLANK")
ReDim arr(UBound(x))
ReDim arrF(UBound(x))
For i = 1 To UBound(x)
addr = Split(Split(x(i), ")")(0), "(")(1)
Debug.Print addr
'if(OR($B93=0,0<COUNTBLANK($B93)))
arr(i) = "ISBLANK(" & addr & ")"
Debug.Print arr(i)
arrF(i) = "IF(OR($" & addr & "=0,0<COUNTBLANK($" & addr & ")))"
Debug.Print arrF(i)
Next
For i = 1 To UBound(x)
t = Replace(t, arr(i), arrF(i))
Next
cel.Offset(, 1).Formula = Right(t, Len(t) - 1)
Next cel
End Sub

[theandrewgor]

Your code works Amazingly! I am sorry it took so long to get back to you. I had to remove the bold IF below. Now how it writes, if i click on cell A1 and run the macro, it writes the fix in cell B1. Could it replace the cell value of the existing cell in all cells that have isblank? also it is pasting everything except the '=' first character, so:
=IF(ISBLANK(B3),"A",VLOOKUP(B33,Type,2,0))
next celll
IF((OR($B3=0,0<COUNTBLANK($B3))),"A",VLOOKUP(B33,Type,2,0))

needs to paste in the same cell:
=IF((OR($B3=0,0<COUNTBLANK($B3))),"A",VLOOKUP(B33,Type,2,0))

and loop through all cells instead of just the one highlighted..

amazing work though!!!


I think this is giving the correct substitution, but maybe some fine tuning is required



Sub test()
Dim arr(), arrF()
Dim t As String, f As String
Dim x, addr
Dim cel As Range

For Each cel In Selection
t = cel.Formula
x = Split(t, "ISBLANK")
ReDim arr(UBound(x))
ReDim arrF(UBound(x))
For i = 1 To UBound(x)
addr = Split(Split(x(i), ")")(0), "(")(1)
Debug.Print addr
'if(OR($B93=0,0<COUNTBLANK($B93)))
arr(i) = "ISBLANK(" & addr & ")"
Debug.Print arr(i)
arrF(i) = "IF(OR($" & addr & "=0,0<COUNTBLANK($" & addr & ")))"
Debug.Print arrF(i)
Next
For i = 1 To UBound(x)
t = Replace(t, arr(i), arrF(i))
Next
cel.Offset(, 1).Formula = Right(t, Len(t) - 1)
Next cel
End Sub

[mdmackillop]

To paste with = in the selected cell, change the formula line to
cel.Formula = t
You can select more than one cell. The code will loop through the selection.

[theandrewgor]

To paste with = in the selected cell, change the formula line to
cel.Formula = t
You can select more than one cell. The code will loop through the selection.

The selecting Multiple is awesome, but it hits a range error if i select any that dont have isblank in them, so i have to only select ones i know have isblank. And switching t = cel.Formula to cel.Formula = t throws range errors, doesnt add = back or replace the data....

Sorry, I am missing something! :) thanks again, you are super.

[mdmackillop]

Can you post your workbook. The sample you provided doesn't work properly.

[theandrewgor]

Can you post your workbook. The sample you provided doesn't work properly.

See D41 for a good sample with multiple isblank statements, here is a real chunk of inputs and formulas.
19259

heres a link to my google drive, if the attachment wont download:
https://drive.google.com/open?id=0Bx2HEayr7CNAR1RlQVhrLW5nVFE

Again, it works, almost, but I would only be able to select cells that have isblank otherwise it fails range test. and it is replacing in the cell to the right instead of itself, and it isn't keeping the = sign. I think that is it.


Thank you so much!

[mdmackillop]

The is obviously an issue with the replacement formula. Can you post what the corrected formula is for that cell so I can compare it with the generated one.

[theandrewgor]

So to reiterate. It does the replace just fine but it does it in the cell to the right instead of replacing the cell it was in. And it isnt a formula anymore because the = isnt carried into it. And per your feedback of selecting the cells i want to run it on, if I select D40-44 it throws a range error because D40 doesnt have something to replace (I think that is why). Is it possible to just skip a cell if it isnt needing work?

D41 says currently:
=IF(OR(ISBLANK(B39),ISBLANK(B40),ISBLANK(B41)),"N",IF($B$38="Y","R"&C40&C39,C41&C40&C39))
What your macro is doing (which is amazing, just missing the = and putting it in the wrong cell):
IF(OR((OR($B39=0,0<COUNTBLANK($B39))),(OR($B40=0,0<COUNTBLANK($B40))),(OR($B41=0,0<COUNTBLANK($B41)))),"N",IF($B$38="Y","R"&C40&C39,C41&C40&C39))
What it should be doing (in the cell it originated in):
=IF(OR((OR($B39=0,0<COUNTBLANK($B39))),(OR($B40=0,0<COUNTBLANK($B40))),(OR($B41=0,0<COUNTBLANK($B41)))),"N",IF($B$38="Y","R"&C40&C39,C41&C40&C39))

[mdmackillop]

It would assist if there was data in Column B which will produce a meaningful result.

[theandrewgor]

It would assist if there was data in Column B which will produce a meaningful result.

Ok, I filled in some relevant data, however, you'd need all of the tables for it to be relevant. Also, I am not sure it is relevant, as the formula is like 95% right, the thing was to replace isblank, as you did, it just needs to write to the correct cell and have the equals sign and skip cells that dont matter. You fixed the main problem of recycling the data, stripping out the data between isblank and ) and using it in the formulas.

Thank you!

19263

[mdmackillop]

Testing on that data showed some doubling of $ signs. This change should remedy that. It will also show the text of the formula in the adjoining cell and attempt to add the formula in the selected cell. The last bit will fail if there is an error in the formula, (which there appears to be), but I can't pin it down.

t = Replace(t, "$$", "$")
cel.Offset(, 1).Formula = Right(t, Len(t) - 1)
cel.Formula = t

[theandrewgor]

You are saying do this? It seems to work for the cell I select, not all cells, but also the cell to the right of it gets all characters before the = with the way I have followed. Probably didn't read your instructions right.

Thank you!



Sub test()
Dim arr(), arrF()
Dim t As String, f As String
Dim x, addr
Dim cel As Range

For Each cel In Selection
t = cel.Formula
x = Split(t, "ISBLANK")
ReDim arr(UBound(x))
ReDim arrF(UBound(x))
For i = 1 To UBound(x)
addr = Split(Split(x(i), ")")(0), "(")(1)
Debug.Print addr
'(OR($B93=0,0<COUNTBLANK($B93)))
arr(i) = "ISBLANK(" & addr & ")"
Debug.Print arr(i)
arrF(i) = "(OR($" & addr & "=0,0<COUNTBLANK($" & addr & ")))"
Debug.Print arrF(i)
t = Replace(t, "$$", "$")
Next
For i = 1 To UBound(x)
t = Replace(t, arr(i), arrF(i))
Next
cel.Offset(, 1).Formula = Right(t, Len(t) - 1)
cel.Formula = t
Next cel
End Sub

[mdmackillop]

The added IF statement appears to be the problem. Omitting this does give a result, I'll leave you to check if it is correct. Modified cells are highlighted yellow.

Sub test()
Dim arr(), arrF()
Dim t As String, f As String
Dim x, addr
Dim cel As Range
Dim i As Long

For Each cel In Selection
If cel.Formula <> "" Then
t = cel.Formula
On Error Resume Next
x = Split(t, "ISBLANK")
On Error GoTo 0
If UBound(x) > 0 Then
ReDim arr(UBound(x))
ReDim arrF(UBound(x))
For i = 1 To UBound(x)
addr = Split(Split(x(i), ")")(0), "(")(1)
arr(i) = "ISBLANK(" & addr & ")"
arrF(i) = "OR($" & addr & "=0,0<COUNTBLANK($" & addr & "))"
Next
For i = 1 To UBound(x)
t = Replace(t, arr(i), arrF(i))
Next
t = Replace(t, "$$", "$")
cel.Formula = t
cel.Interior.ColorIndex = 6
End If
End If
Next cel
End Sub

[theandrewgor]

As far as I can tell, this is amazing! :) Thank you. I am trying to run a series of fixes on a bunch of projects, and without this one, it would make the procedure ridiculously long. I will report back if something comes up, but you are so helpful!

[theandrewgor]

As far as I can tell, this is amazing! :) Thank you. I am trying to run a series of fixes on a bunch of projects, and without this one, it would make the procedure ridiculously long. I will report back if something comes up, but you are so helpful!

So, just to update. I removed the $ from "or($" and from the other statement because sometimes we address a named range and that wouldnt work with the additional $.... :)
Just updating no new questions.

Thank you so much!

[theandrewgor]

SO, Follow up question! :)
Is what you used Regular expression? how would I do what you just did on the following? search all cells for where it says "9 or More",9,*Address*)) and replace with "9 or More",9,value(*Address*)))
so the cell would say something like =IF(B14="9 or More",9,B14),1,0)
and It would need to say something like =IF(B14="9 or More",9,value(B14)),1,0)


It seems like a simple modification of the previous macro, but I am hung up, I have updated the split, included chr(34) to account for " and so on, but it isnt liking my changes.

Thank you!

[theandrewgor]

Incase anyone is looking for some more splits samples, I figured it out. :)


Sub nineor() 'this one is for townrow

Dim arr(), arrF()
Dim t As String, f As String
Dim x, addr
Dim cel As Range
Dim i As Long

For Each cel In Sheets("main").Range("A2:AZ500")
If cel.Formula <> "" Then
t = cel.Formula
On Error Resume Next
x = Split(t, "9 or More")
On Error GoTo 0
If UBound(x) > 0 Then
ReDim arr(UBound(x))
ReDim arrF(UBound(x))
For i = 1 To UBound(x)
addr = Split(Split(x(i), ")")(0), ",9,")(1)
arr(i) = "9 or More" & Chr(34) & ",9," & addr & ")"
arrF(i) = "9 or More" & Chr(34) & ",9,value(" & addr & "))"
Next
For i = 1 To UBound(x)
t = Replace(t, arr(i), arrF(i))
Next
t = Replace(t, "$$", "$")
cel.Formula = t
cel.Interior.ColorIndex = 39
End If
End If
Next cel
End Sub
[/QUOTE]