Loop through worksheets, exclude some and look-up cellvalue-CodePudding

I am a beginner in excel vba and could use your help a lot!

I encountered the following problems with my macro's:

I have an excel workbook with several worksheets. I want to exclude some of these. For the other sheets I would like the macro to look at the cell value of each worksheet. In this case "A1". 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 and the following worksheet can be viewed.

I have the following two macro's:

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 I keep running into now 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.

Can someone help me?

Thanks in advance!

CodePudding user response：

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

CodePudding user response：

The cleanest way to do this with the code in its current form would be to pass the sheet object to the other sub:

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(sh)

    End Select
Next sh
End Sub

and then

Sub X2(ByVal sh As Worksheet)

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

End Sub

I've assumed you have a reason in the real-world use for having separate sub-routines, but once you understand this concept of passing the objects around then I would suggest just doing this in a single routine.