May 04 2021 02:26 PM - edited May 04 2021 02:29 PM
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!
May 04 2021 02:32 PM
May 04 2021 02:46 PM
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.
May 04 2021 03:15 PM
May 04 2021 03:26 PM
May 04 2021 03:53 PM
May 04 2021 04:11 PM
May 04 2021 04:18 PM
@brianrichard1120 I figured out how to post this @JMB17 @Hans Vogelaar and anyone willing to help.
May 04 2021 04:21 PM
May 04 2021 04:53 PM
May 04 2021 05:02 PM - edited May 04 2021 05:03 PM
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
May 04 2021 05:31 PM
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:
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)
May 04 2021 05:37 PM
May 04 2021 05:45 PM