Search code examples
excelvbaloopsworksheet

Loop through worksheets, exclude some and look-up cell value


I have an Excel workbook with several worksheets.

I would like the macro to look at the value of cell "A1" of each worksheet.
If the cell value is less than 8, A1 must be adjusted to 8.
If the cell value is greater than 8, nothing needs to be adjusted.

I have two macros:

Sub LoopCertain() 'Excel VBA to exclude sheets(1-3)
Dim sh As Worksheet

For Each sh In Sheets
    Select Case sh.Name
    Case Is = "Blad1", "Blad2", "Blad3"
        'No Code here if excluded
    Case Else
        Call X2
    End Select
Next sh

End Sub

and

Sub X2()
'declare a variable
Dim ws As Worksheet
Set ws = ActiveSheet

'calculate if a cell is less than a specific value
If ws.Range("A1") < 8 Then
    ws.Range("A1") = 8
Else

End If

End Sub

The problem is that only the active worksheet is done and the rest of the worksheets are not looked at. The macro also does not check whether the worksheet should not be included.


Solution

  • If you want using two subs, please try the next way. Your code only use the active sheet in the second sub:

    Sub LoopCertain() 'Excel VBA to exclude sheets(1-3)
     Dim sh As Worksheet
    
     For Each sh In Sheets
        Select Case sh.name
            Case "Blad1", "Blad2", "Blad3"
                 'No Code here if excluded
            Case Else
                Call X2(sh)
        End Select
     Next sh
    End Sub
    
    Sub X2(ws As Worksheet)
     'calculate if a cell is less than a specific value
      If ws.Range("A1").value < 8 Then ws.Range("A1") = 8
    End Sub
    

    But for such a simple processing, no need of the second since everything can be done in the first one:

    Sub LoopCertain() 'Excel VBA to exclude sheets(1-3)
     Dim sh As Worksheet
    
     For Each sh In Sheets
        Select Case sh.name
            Case "Blad1", "Blad2", "Blad3"
                 'No Code here if excluded
            Case Else
                If sh.Range("A1").value < 8 Then sh.Range("A1") = 8        
        End Select
     Next sh
    End Sub