PLEASE HELP ENGINEERS

Copper Contributor

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.

@brianrichard1120 

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 Sub

And 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.
I wonder if 2105 will fix this. I sure hope so! https://docs.microsoft.com/en-us/officeupdates/current-channel-preview
The more obvious difference, to me, is that Hans is not selecting the range. Your issue may be merged cells that are causing the selected range to expand beyond what you are expecting.

If you tested Hans's suggestion and it did not work, then perhaps you could upload a sample workbook with sensitive information removed? But, if you've not tried it yet, you should test his suggestion first.
Thank you for your response and pointing out that difference. I just attempted Hans solution and was met with the same problem unfortunately. My data varies in size but I am doing testing on a simple data set. I will post once I figure out how.

@brianrichard1120 I figured out how to post this @JMB17 @Hans Vogelaar and anyone willing to help. 

Looking at this I understand my find and replace was only supposed to work on column L. You can change it to one of the first few columns there to work. But either way the error is still evident.
It does not appear that the sample data contains any of the characters the macro is looking for?

I added some of the characters to be replaced to the data in columns A-E and added some data containing the characters to be replaced in column L. It does not appear that I'm able to replicate the problem - the macro found and replaced the specified characters in Column L without bothering any of the other data.

By chance, do you have any other vba code that may be interfering, such as a worksheet_change event handler (or some other event handler) that may be getting triggered by the find/replace action?

I truly appreciate your continued attention to this issue here. I have no other interference. Sometimes the sheet does not contain those characters, but when it does I need to address it so that’s why it’s in there. My main concern is to fill in the blank values in the specific column that I specify. Would you mind changing the column of attention to column b in the sheet test and running the code? Theoretically it should just fill in the blank cells in column b with the word BLANK. (My mistake at having it look at column l) just to explain what mine is doing when I run it... it is filling in every blank cell in the used range within the whole workbook with the word BLANK

@JMB17 I am running 2104 btw.

@brianrichard1120 

 

Yes, changing the code to look at column b:

 

Sub CleanAC()

    Dim CharacterArray As Variant
    CharacterArray = Array(Chr(58), Chr(92), Chr(47), "~?", "~*", Chr(91), Chr(93))
    Dim Character As Variant
    
    Sheets("Test").Activate
    With Sheets("Test")
        LastRowPYA = Cells(Rows.Count, "A").End(xlUp).Row
        With Range("B1:B" & 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

End Sub

 

 

results in this:

 

JMB17_1-1620174060068.png

 

But, I'm working on office 2016. 

 

As a side note, there would usually be a period before "cells", "rows", and "range" to tell vba you are referring to the object in the "with" statement. In this specific case, it shouldn't matter because you are activating  the "test" worksheet first and unqualified references will refer to the active sheet. But, if the activate statement were removed, then the object references would be a mixture between the "test" worksheet and whatever the active sheet was.

 

LastRowPYA = .Cells(.Rows.Count, "A").End(xlUp).Row
With .Range("B1:B" & LastRowPYA)

 

Understood. I appreciate that explanation regarding the . And active sheets. I am convinced that something in excel has broke causing this error. I do hope that other readers will run my test file I posted and hopefully replicate my error. I will post what mine looks like when I have a chance tomorrow. Wish it produced the same results as yours :(.
I have brought this to the attention of Microsoft support yesterday and earlier today and they shrugged me off. That’s why I have resorted to this community to help and also hope that a Microsoft employee sees these posts.