Forum Discussion
PLEASE HELP ENGINEERS
My code has worked for a few months and all of the sudden stopped working. My macro now does find and replace for the whole used range for my data. Here is the code:
Sub CleanAC()
Dim CharacterArray As Variant
CharacterArray = Array(Chr(58), Chr(92), Chr(47), "~?", "~*", Chr(91), Chr(93))
Dim Character As Variant
Sheets("Invoices").Activate
With Sheets("Invoices")
LastRowPYA = Cells(Rows.Count, "A").End(xlUp).Row
Range("AB1:AB" & LastRowPYA).Select
With Selection
.Replace What:=Null, Replacement:="BLANK", LookAt:=xlPart
For Each Character In CharacterArray
.Replace What:=Character, Replacement:=Chr(32), LookAt:=xlPart
Next
End With
End With
End Sub
This should only be looking at column AB and it does find and replace on the whole worksheets used range!
14 Replies
- Disclaimer: This function has been used and operational for 1 month + and has all of the sudden stopped working. Is it something Microsoft did that caused this to stop working. 250+ business owners rely on this function in conjunction with many others to generate reports for themselves.
Does this work better?
Sub CleanAC() Dim LastRowPYA As Long Dim CharacterArray As Variant Dim Character As Variant CharacterArray = Array(Chr(58), Chr(92), Chr(47), "~?", "~*", Chr(91), Chr(93)) Application.ScreenUpdating = False With Worksheets("Invoices") LastRowPYA = .Cells(.Rows.Count, "A").End(xlUp).Row With .Range("AB1:AB" & LastRowPYA) .Replace What:=Null, Replacement:="BLANK", LookAt:=xlPart For Each Character In CharacterArray .Replace What:=Character, Replacement:=Chr(32), LookAt:=xlPart Next Character End With End With Application.ScreenUpdating = True End SubAnd perhaps you should change
LastRowPYA = .Cells(.Rows.Count, "A").End(xlUp).Row
to
LastRowPYA = .Cells(.Rows.Count, "AB").End(xlUp).Row
but I cannot judge that.
- Thank you for the response. Did you have success running this code here?
Only differences I see are the screen updating and "Next Character."
Any thoughts on why my code has worked well for so long and all the sudden started crashing on various computers on Saturday morning? It was like a switch was flipped and it no longer is valid code.
