+ Reply to Thread
Results 1 to 4 of 4

Looping Macro through Specific Worksheets

Hybrid View

  1. 11-30-2015, 09:29 AM #1
    MsBBStacker
    MsBBStacker is offline
    Registered User
    Join Date
    10-22-2015
    Location
    Oneonta, ny
    MS-Off Ver
    2010
    Posts
    30

    Cool Looping Macro through Specific Worksheets

    Hello,

    I've searched online for a macro that will repeat itself for all worksheets. What I have so far will loop through all the worksheets but I have to click on a message box to continue to the next worksheet. Even though I run this, it doesn't replace everything that needs to be replaced either. Any advice for getting this to loop correctly? I don't need it to search my two tabs that are titled "Srch" and "Rand". If it's possible to avoid those, it would be appreciated.

    Sub ARplcAllChrWkshts()
'
Dim WS_Count As Integer
Dim I As Integer

WS_Count = ActiveWorkbook.Worksheets.Count
For I = 3 To WS_Count

Cells.Replace What:="Char65A", Replacement:="=Char(65)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char66B", Replacement:="=Char(66)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char67C", Replacement:="=Char(67)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char68D", Replacement:="=Char(68)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char69E", Replacement:="=Char(69)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char70F", Replacement:="=Char(70)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char71G", Replacement:="=Char(71)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char72H", Replacement:="=Char(72)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char73I", Replacement:="=Char(73)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char74J", Replacement:="=Char(74)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char75K", Replacement:="=Char(75)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char76L", Replacement:="=Char(76)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char77M", Replacement:="=Char(77)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char78N", Replacement:="=Char(78)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char79O", Replacement:="=Char(79)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char80P", Replacement:="=Char(80)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char81Q", Replacement:="=Char(81)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char82R", Replacement:="=Char(82)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char83S", Replacement:="=Char(83)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char84T", Replacement:="=Char(84)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char85U", Replacement:="=Char(85)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char86V", Replacement:="=Char(86)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char87W", Replacement:="=Char(87)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char88X", Replacement:="=Char(88)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char89Y", Replacement:="=Char(89)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char90Z", Replacement:="=Char(90)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char48Zero", Replacement:="=Char(48)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char49One", Replacement:="=Char(49)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char50Two", Replacement:="=Char(50)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char51Three", Replacement:="=Char(51)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char52Four", Replacement:="=Char(52)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char53Five", Replacement:="=Char(53)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char54Six", Replacement:="=Char(54)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char55Seven", Replacement:="=Char(55)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char56Eight", Replacement:="=Char(56)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Char57Nine", Replacement:="=Char(57)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="A", Replacement:="=Char(39)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="B", Replacement:="=Char(45)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="C", Replacement:="=Char(160)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="D", Replacement:="=Char(33)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="E", Replacement:="=Char(34)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="F", Replacement:="=Char(35)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="G", Replacement:="=Char(36)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="H", Replacement:="=Char(37)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="I", Replacement:="=Char(38)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="J", Replacement:="=Char(40)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="K", Replacement:="=Char(41)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="L", Replacement:="=Char(42)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="M", Replacement:="=Char(44)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="N", Replacement:="=Char(46)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="O", Replacement:="=Char(47)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="P", Replacement:="=Char(58)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Q", Replacement:="=Char(59)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="R", Replacement:="=Char(63)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="S", Replacement:="=Char(64)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="T", Replacement:="=Char(91)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="U", Replacement:="=Char(92)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="V", Replacement:="=Char(93)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="W", Replacement:="=Char(94)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="X", Replacement:="=Char(95)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Y", Replacement:="=Char(123)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="Z", Replacement:="=Char(124)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="1", Replacement:="=Char(125)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="2", Replacement:="=Char(126)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="3", Replacement:="=Char(43)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="4", Replacement:="=Char(60)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="5", Replacement:="=Char(61)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="6", Replacement:="=Char(62)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="7", Replacement:="=Char(133)", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

MsgBox ActiveWorkbook.Worksheets(I).Name

Next I

End Sub
    In case you're wondering, I'm creating a cypher and it won't work if the user runs the VBA code to Find All of symbols so I've temporarily replaced these symbols with a letter and a number and set it to replace these.

    Thanks,
    ~BBS
    Last edited by MsBBStacker; 11-30-2015 at 11:02 AM.
    Reply With QuoteRegister To Reply
  2. 11-30-2015, 09:42 AM #2
    Keebellah
    Keebellah is offline
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,937

    Re: Looping Macro through Specific Worksheets

    Hi, the messagebox is aa question of deleteng that line of code, but the way you have written your code, well, that's not the way, there may be many ways to do this but this one works and ignores the two worksheets named "Srch" and "Rand"
    Sub ARplcAllChrWkshts()
Dim ws  As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Srch" And ws.Name <> "Rand" Then
        With ws
            .Cells.Replace What:="Char65A", Replacement:="=Char(65)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char66B", Replacement:="=Char(66)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char67C", Replacement:="=Char(67)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char68D", Replacement:="=Char(68)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char69E", Replacement:="=Char(69)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char70F", Replacement:="=Char(70)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char71G", Replacement:="=Char(71)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char72H", Replacement:="=Char(72)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char73I", Replacement:="=Char(73)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char74J", Replacement:="=Char(74)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char75K", Replacement:="=Char(75)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char76L", Replacement:="=Char(76)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char77M", Replacement:="=Char(77)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char78N", Replacement:="=Char(78)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char79O", Replacement:="=Char(79)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char80P", Replacement:="=Char(80)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char81Q", Replacement:="=Char(81)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char82R", Replacement:="=Char(82)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char83S", Replacement:="=Char(83)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char84T", Replacement:="=Char(84)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char85U", Replacement:="=Char(85)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char86V", Replacement:="=Char(86)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char87W", Replacement:="=Char(87)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char88X", Replacement:="=Char(88)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char89Y", Replacement:="=Char(89)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char90Z", Replacement:="=Char(90)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char48Zero", Replacement:="=Char(48)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char49One", Replacement:="=Char(49)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char50Two", Replacement:="=Char(50)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char51Three", Replacement:="=Char(51)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char52Four", Replacement:="=Char(52)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char53Five", Replacement:="=Char(53)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char54Six", Replacement:="=Char(54)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char55Seven", Replacement:="=Char(55)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char56Eight", Replacement:="=Char(56)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Char57Nine", Replacement:="=Char(57)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="A", Replacement:="=Char(39)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="B", Replacement:="=Char(45)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="C", Replacement:="=Char(160)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="D", Replacement:="=Char(33)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="E", Replacement:="=Char(34)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="F", Replacement:="=Char(35)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="G", Replacement:="=Char(36)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="H", Replacement:="=Char(37)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="I", Replacement:="=Char(38)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="J", Replacement:="=Char(40)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="K", Replacement:="=Char(41)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="L", Replacement:="=Char(42)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="M", Replacement:="=Char(44)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="N", Replacement:="=Char(46)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="O", Replacement:="=Char(47)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="P", Replacement:="=Char(58)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Q", Replacement:="=Char(59)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="R", Replacement:="=Char(63)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="S", Replacement:="=Char(64)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="T", Replacement:="=Char(91)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="U", Replacement:="=Char(92)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="V", Replacement:="=Char(93)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="W", Replacement:="=Char(94)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="X", Replacement:="=Char(95)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Y", Replacement:="=Char(123)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="Z", Replacement:="=Char(124)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="1", Replacement:="=Char(125)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="2", Replacement:="=Char(126)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="3", Replacement:="=Char(43)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="4", Replacement:="=Char(60)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="5", Replacement:="=Char(61)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="6", Replacement:="=Char(62)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
            .Cells.Replace What:="7", Replacement:="=Char(133)", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                ReplaceFormat:=False
        End With
    End If
Next ws
End Sub
    Hope this one helps. I tested it and it replaced the values I placed
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you
    Reply With QuoteRegister To Reply
  3. 11-30-2015, 09:42 AM #3
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Looping Macro through Specific Worksheets

    Try this.
    Sub ARplcAllChrWkshts()
    '
Dim WS_Count As Long
Dim I As Long
Dim J As Long

    WS_Count = ActiveWorkbook.Worksheets.Count
    For I = 3 To WS_Count
        With ActiveWorkbook.workshets(I)

            For J = 65 To 90
                .Cells.Replace What:="Char" & J & Chr(J), Replacement:="=Char(" & J & ")", LookAt:=xlWhole, _
                               SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                               ReplaceFormat:=False
            Next J

            .Cells.Replace What:="Char48Zero", Replacement:="=Char(48)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="Char49One", Replacement:="=Char(49)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="Char50Two", Replacement:="=Char(50)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="Char51Three", Replacement:="=Char(51)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="Char52Four", Replacement:="=Char(52)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="Char53Five", Replacement:="=Char(53)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="Char54Six", Replacement:="=Char(54)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="Char55Seven", Replacement:="=Char(55)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="Char56Eight", Replacement:="=Char(56)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="Char57Nine", Replacement:="=Char(57)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="A", Replacement:="=Char(39)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="B", Replacement:="=Char(45)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="C", Replacement:="=Char(160)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="D", Replacement:="=Char(33)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="E", Replacement:="=Char(34)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="F", Replacement:="=Char(35)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="G", Replacement:="=Char(36)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="H", Replacement:="=Char(37)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="I", Replacement:="=Char(38)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="J", Replacement:="=Char(40)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="K", Replacement:="=Char(41)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="L", Replacement:="=Char(42)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="M", Replacement:="=Char(44)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="N", Replacement:="=Char(46)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="O", Replacement:="=Char(47)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="P", Replacement:="=Char(58)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="Q", Replacement:="=Char(59)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="R", Replacement:="=Char(63)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="S", Replacement:="=Char(64)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="T", Replacement:="=Char(91)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="U", Replacement:="=Char(92)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="V", Replacement:="=Char(93)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="W", Replacement:="=Char(94)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="X", Replacement:="=Char(95)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="Y", Replacement:="=Char(123)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="Z", Replacement:="=Char(124)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="1", Replacement:="=Char(125)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="2", Replacement:="=Char(126)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="3", Replacement:="=Char(43)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="4", Replacement:="=Char(60)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="5", Replacement:="=Char(61)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="6", Replacement:="=Char(62)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
            .Cells.Replace What:="7", Replacement:="=Char(133)", LookAt:=xlWhole, _
                           SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
                           ReplaceFormat:=False
        End With


    Next I

End Sub
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  4. 11-30-2015, 10:30 AM #4
    MsBBStacker
    MsBBStacker is offline
    Registered User
    Join Date
    10-22-2015
    Location
    Oneonta, ny
    MS-Off Ver
    2010
    Posts
    30

    Re: Looping Macro through Specific Worksheets

    Thanks for the answers and I do appreciate the help. Will try it now.

    Edit:

    Both worked great! Thanks! Norie, how does your code work if you don't mind me asking. I've been searching VBA tutorials for different ways to work with VBA but I haven't seen anything like your code yet. My current problem is just trying to figure out how each command works without getting stuck in a never-ending loop (accidently added one and had to ctrl alt del out of excel.
    Last edited by MsBBStacker; 11-30-2015 at 10:56 AM. Reason: Just added to the original version.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Looping through specific worksheets
    By gsandy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-01-2015, 03:52 PM
  2. Macro to email specific worksheets to specific recipients
    By LeanAccountant in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-10-2015, 12:22 PM
  3. Need help in looping of Macro between cells and worksheets
    By Manish_Gupta in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-04-2013, 06:07 PM
  4. Macro looping through several worksheets in one workbook
    By awcwa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-04-2013, 05:26 PM
  5. Problems with looping macro through specific folder
    By kchm_2000 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 09-09-2012, 07:00 AM
  6. Fill down macro looping through all worksheets
    By jeffreybrown in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-24-2010, 08:11 PM
  7. [SOLVED] looping through specific worksheets
    By simora in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-28-2005, 08:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1