I have a bigger macro which after the calculations have been done, needs to do a Text to column on that column of the formulas.
Before I do this text to column I first convert all the formulas to values except the first formula (want to keep that for reference).
My excel uses ' ; ' in formulas and after the text to column the formula is changed to be separated by ' , '.
Now of course that formula will not work anymore.
So I wanted to change it all back again with a simple replace in VBA.
But this is not working somehow.
Strange thing is that even when I record the macro i will not work when I use that recorded macro.
I attached a workbook. It has 2 codes (1 of 2 is blocked).
Both I would expect to work but they don't.
The blocked one is the one I recorded.
Sub tst()
'Correct Formula in U
Range("A2").Replace What:=",", Replacement:=";", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A2").Replace What:=",", Replacement:=";", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
' ActiveCell.Replace What:=",", Replacement:=";", LookAt:=xlPart, _
' SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
' ReplaceFormat:=False
' Cells.Find(What:=",", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
' xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
' , SearchFormat:=False).Activate
End Sub
Bookmarks