VBA for All cells in workbook

[Curt]

I would like a VBA that replaces every cell of every sheet in a workbook with
its output when used with the following function:

=TRIM(SUBSTITUTE(A1,CHAR(160),CHAR(32)))

thanks

Curt J

 

[Gary''s Student]

Sub GlobalChange()
Dim w As Worksheet
For Each w In ActiveWorkbook.Sheets
w.Activate
For Each r In ActiveSheet.UsedRange
r.Value = Trim(Replace(r.Value, Chr(160), Chr(32)))
Next
Next
End Sub

 

[JLGWhiz]

Sub liminal()
Dim c As Range
For Each sh In ThisWorkbook.Sheets
For Each c In sh.UsedRange
If c.Formula = "=TRIM(SUBSTITUTE(" & _
c.Address(False, False) & ",CHAR(160),CHAR(32)))" Then
c = c.Value
End If
Next
Next
End Sub

 

[Dave Peterson]

Instead of looping through each cell, you could do the equivalent of
edit|replace:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long
dim Wks as worksheet

myBadChars = Array(Chr(160))

myGoodChars = Array(" ")

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

for each wks in activeworkbook.worksheets
For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr
next wks

End Sub

This was left over from a previous post. I left it as-is (using arrays). I
thought that it would be useful if you decide you wanted to replace other
characters, too.

 

[Dave Peterson]

ps. This doesn't apply the trim() portion.

Instead of looping through each cell, you could do the equivalent of
edit|replace:

Option Explicit
Sub cleanEmUp()

Dim myBadChars As Variant
Dim myGoodChars As Variant
Dim iCtr As Long
dim Wks as worksheet

myBadChars = Array(Chr(160))

myGoodChars = Array(" ")

If UBound(myGoodChars) <> UBound(myBadChars) Then
MsgBox "Design error!"
Exit Sub
End If

for each wks in activeworkbook.worksheets
For iCtr = LBound(myBadChars) To UBound(myBadChars)
ActiveSheet.Cells.Replace What:=myBadChars(iCtr), _
Replacement:=myGoodChars(iCtr), _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
Next iCtr
next wks

End Sub

This was left over from a previous post. I left it as-is (using arrays). I
thought that it would be useful if you decide you wanted to replace other
characters, too.

 

[Curt]

Thank you for the reply. I am a beginner so I might have phrased my question
incorrectly, but is there a reason that this macro works when I use it for
"thisWorkbook" but it doesn't when I put it as a module in my personal macro
workbook?

thanks