Howard wrote:





Option Explicit

Sub TheCountOfFour()

Dim wks As Worksheet
Dim rng As Range, c As Range
Dim i As Variant, ii As Long
Dim j As Variant, jj As Long
Dim k As Variant, kk As Long
Dim l As Variant, ll As Long

i = Range("F1").Value
j = Range("F2").Value
k = Range("F3").Value
l = Range("F4").Value

Set rng = Range("A1:A250")


i,j,k,l and rng will refer to activesheet
so every time when you run macro you will read values from unknown
active sheet.
Unless that is what you wanted to do. i.e. every sheet has some values
defined in F1 .. F4 and depending which sheet is active you read
different values.




For Each wks In ActiveWorkbook.Worksheets
For Each c In rng
If c.Value = i Then ii = ii + 1
If c.Value = j Then jj = jj + 1
If c.Value = k Then kk = kk + 1
If c.Value = l Then ll = ll + 1
Next
Next wks

you never use wks so why you define it?

if your idea was to check "a1:a250" range on every sheet do that

For Each wks In ActiveWorkbook.Worksheets
set rng = wks.range ("a1:a250")
For Each c In rng




MsgBox "You have " & ii & " " & i & ", " & " " _
& jj & " " & j & ", " & " " _
& kk & " " & k & ", " & " " _
& ll & " " & l, vbOKOnly, "Count Four"

' Range("G1").Value = ii
' Range("G2").Value = jj
' Range("G3").Value = kk
' Range("G4").Value = ll



ranage ("G1") on what sheet ?

a) active sheet?
b) specific sheet. in that case use worksheets("name").range