Trouble getting function to run once to alert across all worksheets

Question

I am trying to run a Range code that If any value in a range is <1, it will alert the user. After trying multiple codes, this is the only one that doesn't run an error code. HOWEVER, it runs each page individually and is giving an incorrect answer. Please help!

Option Explicit
Sub Main()

Dim WS_Count As Integer
Dim i As Integer

WS_Count = ActiveWorkbook.Worksheets.Count
For i = 1 To 4

With WorksheetFunction
If .CountIf(Range("K3:K20"), "<1") > 0 Then
MsgBox "Rotations are needed"
Else
MsgBox "Rotations not needed"
End If
If .CountIf(Range("L3:L20"), "<1") > 0 Then
MsgBox "Functions are needed"
Else
MsgBox "Functions are NOT needed"
End If

End With
Next i


End Sub

You need to qualify the `Range("K3:K20")` and `Range("L3:L20")` with the worksheet they're on (implicitly the `ActiveSheet` currently). — BigBen, Jun 24 '21 at 19:07
Do you need to run the code on all sheets, for that specific ranges? — FaneDuru, Jun 24 '21 at 19:09
I need the code to run all sheets, for that range and end up with one cohesive answer. I'm new to excel vba and have been having issues writing the code for each worksheets range. — Kenna, Jun 24 '21 at 19:14
Try declaring a sheet variable, iterate between all sheets and qualify the range according to that sheet. I will post an answer to show you how to do it. No need to make it accepted answer, even if it does what you need. — FaneDuru, Jun 24 '21 at 19:26
Didn't you find some time to test the code I posted? If tested, didn't it do what you need? — FaneDuru, Jun 24 '21 at 19:43

score 0 · Answer 1 · answered Jun 24 '21 at 19:28

Please, try the next code:

Sub Main()
Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
    With WorksheetFunction
        If .CountIf(sh.Range("K3:K20"), "<1") > 0 Then
            MsgBox "Rotations are needed in sheet """ & sh.Name & """."
        Else
            MsgBox "Rotations NOT needed in sheet """ & sh.Name & """."
        End If
        If .CountIf(sh.Range("L3:L20"), "<1") > 0 Then
            MsgBox "Functions are needed in sheet """ & sh.Name & """."
        Else
            MsgBox "Functions are NOT needed in sheet """ & sh.Name & """."
        End If
    End With
Next
End Sub

score 0 · Answer 2 · answered Jun 25 '21 at 00:34

Alert Across Worksheets

Option Explicit

Sub AlertRotation()
    
    Const rgAddress As String = "K3:K20" ' assumed at least two cells
    Const LessCriteria As Double = 1
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    Dim cCount As Long: cCount = wb.Worksheets.Count
    
    Dim ws As Worksheet: Set ws = wb.Worksheets(1)
    Dim rg As Range: Set rg = ws.Range(rgAddress)
    Dim ColString As String: ColString = Split(rg.Address, "$")(1)
    Dim fRow As Long: fRow = rg.Row
    Dim rOffset As Long: rOffset = fRow - 1
    Dim rCount As Long: rCount = rg.Rows.Count
    Dim Data As Variant: ReDim Data(1 To cCount)
    
    Dim c As Long

    For Each ws In wb.Worksheets
        c = c + 1
        Data(c) = ws.Range(rgAddress).Value
    Next ws
    
    Dim r As Long
    Dim rotCount As Long
    Dim CellAddress As String
    
    For r = 1 To rCount
        For c = 1 To cCount
            If Data(c)(r, 1) < LessCriteria Then
                rotCount = rotCount + 1
                CellAddress = ColString & r + rOffset
                MsgBox "Rotate cell '" & CellAddress & "'."
                Exit For
            End If
        Next c
    Next r

    If rotCount = 0 Then
        MsgBox "No rotations needed."
    End If
    
End Sub

Trouble getting function to run once to alert across all worksheets

2 Answers2

Alert Across Worksheets