Run code only on Tabs containg Keyword

[chungym]

Hi,

I'm trying to learn VBA, and I'm getting stuck on the code that I have. Sorry that it is really amateur. I'd like to run this code that changes all blank cells to zeros in a specific range only on the worksheets who have a specific keyword in the tab name - I'd like the code to run on all tabs containing the word "Finance" as part of the worksheet name. What I have so far only works on the active sheet. It doesn't go through the entire workbook.

Sub AddZero2()
Dim cell As Range
Dim wks As Excel.Worksheet
For Each wks In ThisWorkbook.Worksheets
    If InStr(wks.Name, "Finance") <> 0 Then
    For Each cell In Range("E87:AJ98")
        If Len(cell.Value) = 0 Then
        cell.Value = 0
        End If
    Next cell
 End If
Next wks
End Sub

any help would be appreciated...

 

[vds1]

Try,

Sub AddZero2()
Dim cell As Range
Dim wks As Excel.Worksheet
For Each wks In ThisWorkbook.Worksheets
[COLOR=#ff0000]    wks.Select[/COLOR]
    If InStr([COLOR=#ff0000]LCase([/COLOR]wks.Name[COLOR=#ff0000])[/COLOR], "finance") <> 0 Then
    For Each cell In Range("E87:AJ98")
        If Len(cell.Value) = 0 Then
        cell.Value = 0
        End If
    Next cell
 End If
Next wks
End Sub

 

[chungym]

It still doesn't go through the entire workbook. It only seems to work on the active sheet?

 

[JackDanIce]

Your inner For-Loop wasn't searching through each worksheet. Try:

Sub AddZero2()

Dim cell As Range
Dim wks As Worksheet

Application.ScreenUpdating = False

For Each wks In ActiveWorkbook.Sheets
    With wks
        If InStr(.name, "Finance") Then
            For Each cell In .Range("E87:AJ98")
                If Lenb(cell.Value) = 0 Then cell.Value = 0
            Next cell
        End If
    End With
Next wks

Application.ScreenUpdating = True

End Sub

 

[JackDanIce]

This version removes the inner loop so should run faster:

Sub AddZero2()

Dim cell As Range
Dim wks As Worksheet

Application.ScreenUpdating = False

For Each wks In ActiveWorkbook.Sheets
    With wks
        If InStr(.name, "Finance") Then
            On Error Resume Next
            .Range("E87:AJ98").SpecialCells(xlCellTypeBlanks).Value = 0           
           On Error Goto 0
        End If
    End With
Next wks

Application.ScreenUpdating = True

End Sub

 

[chungym]

That worked perfectly! Thank you so much!!!

This version removes the inner loop so should run faster:

Sub AddZero2()

Dim cell As Range
Dim wks As Worksheet

Application.ScreenUpdating = False

For Each wks In ActiveWorkbook.Sheets
    With wks
        If InStr(.name, "Finance") Then
            On Error Resume Next
            .Range("E87:AJ98").SpecialCells(xlCellTypeBlanks).Value = 0           
           On Error Goto 0
        End If
    End With
Next wks

Application.ScreenUpdating = True

End Sub

 

[JackDanIce]

You're welcome