Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for All cells in workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for All cells in workbook
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 -- Gary''s Student - gsnu201002 "Curt" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for All cells in workbook
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 "Curt" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for All cells in workbook
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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for All cells in workbook
ps. This doesn't apply the trim() portion.
Dave Peterson wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for All cells in workbook
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 "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu201002 "Curt" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy cells based on conditions in one workbook to another workbook | Excel Discussion (Misc queries) | |||
Copy cells based on conditions in one workbook to another workbook | Excel Worksheet Functions | |||
Copy cells based on conditions in one workbook to another workbook | Excel Programming | |||
copy specific cells in workbook to new workbook | Excel Programming | |||
Copy a range of cells in an unopened workbook and paste it to the current workbook | Excel Programming |