Advice on VBA Code which doesnt skip 2 mentioned worksheets

VBA learner ITG

Board Regular
Joined
Apr 18, 2017
Messages
230
Office Version
  1. 365
Hi all,

I was wondering if I could get your advice to why this piece of code isnt ignoring the worksheets called: "Control" & "Data".

This is the piece of code that I cannot understand why it doesnt ignore the worksheets when its ran:

Code:
For Each sht In ActiveWorkbook.Worksheets
        If sht.Name = "control" Or _
           sht.Name = "Data" Then
            'Do Nothing
        Else

FULL CODE BELOW

VBA Code:
Sub Multi_FindReplace()
'PURPOSE: Find & Replace a list of text/values throughout entire workbook from a table

Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant
Dim ReplaceCount As Long

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False

'Create variable to point to your table
  Set tbl = Worksheets("CONTROL").ListObjects("Table1")


'Create an Array out of the Table's Data
  Set TempArray = tbl.DataBodyRange
  myArray = Application.Transpose(TempArray)
 
'Designate Columns for Find/Replace data
  fndList = 1
  rplcList = 2

'Loop through each item in Array lists
  For x = LBound(myArray, 1) To UBound(myArray, 2)
    'Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
      For Each sht In ActiveWorkbook.Worksheets
  
        If sht.Name = "control" Or _
           sht.Name = "Data" Then
            'Do Nothing
        Else
        
        
         ReplaceCount = ReplaceCount + Application.WorksheetFunction.CountIf(sht.Cells, "*" & fnd & "*")
        
                  
          sht.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
            LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False
        
        End If
      Next sht
  Next x


MsgBox "I have completed my search and made replacements in " & ReplaceCount & " cell(s)."


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True

End Sub
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,599
Office Version
  1. 365
Platform
  1. Windows
Are your sheets "control" & "Data" rather than (say) "Control" & "data" as VBA is case sensitive.
Also check that the sheet names do not have any leading/trailing spaces.
 
Solution

VBA learner ITG

Board Regular
Joined
Apr 18, 2017
Messages
230
Office Version
  1. 365
Are your sheets "control" & "Data" rather than (say) "Control" & "data" as VBA is case sensitive.
Also check that the sheet names do not have any leading/trailing spaces.

Hi Fluff,

You were correct after I amended to match and remove trailing and leading spaces the code worked.

Thank you for your time.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,599
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,141,770
Messages
5,708,432
Members
421,568
Latest member
Huxley

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top