Do see why this would return inaccurate counts of the values listed in F1:F4sheet 1? There are three sheets in the workbook. I have checked for leading-trailing spaces, copied and pasted to assure spelling and upper case is same - same.

If I copy and paste Reject in say two sheet. should return 2, maybe will or maybe returns 0. Tried several seneraios to try to detect a trend but it is too random to make any assumptions.

The Msgbox and Range print out depict the same errors.

These are in F1:F4, sheet 1.
Approved
Reject
Touched
PI


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")

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

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

End Sub

Regards,
Howard

"Howard" wrote:
Do see why this would return inaccurate counts
[....]
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

For one thing, rng does not refer to wks. Try:

For Each c In wks.rng

hi Howard,

Am Tue, 5 Mar 2013 12:30:45 -0800 (PST) schrieb Howard:

Do see why this would return inaccurate counts of the values listed in F1:F4sheet 1? There are three sheets in the workbook. I have checked for leading-trailing spaces, copied and pasted to assure spelling and upper case is same - same.

If I copy and paste Reject in say two sheet. should return 2, maybe will or maybe returns 0. Tried several seneraios to try to detect a trend but it is too random to make any assumptions.

The Msgbox and Range print out depict the same errors.

if you don't run the macro from sheet 1 you will get false results.
Therefore change the initializing of i to l:

With Sheets(1)
i = .Range("F1").Value
j = .Range("F2").Value
k = .Range("F3").Value
l = .Range("F4").Value
End With


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Tuesday, March 5, 2013 12:39:34 PM UTC-8, joeu2004 wrote:
"Howard" wrote:

Do see why this would return inaccurate counts

[....]

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



For one thing, rng does not refer to wks. Try:



For Each c In wks.rng

Thanks Joeu and Claus for taking a look.

This errors with method or data not found, with the ".rng" in blue

<For Each c In wks.rng

And per Claus's advice, I am running it from sheet 1.

Howard

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

On Tuesday, March 5, 2013 1:56:33 PM UTC-8, witek wrote:
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


Thanks Witek, Claus and Joeu.
Final version that works well is here.
I appreciate the help.

Regards,
Howard

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

With Sheets(1)
i = .Range("F1").Value
j = .Range("F2").Value
k = .Range("F3").Value
l = .Range("F4").Value
End With

For Each wks In ActiveWorkbook.Worksheets
Set rng = wks.Range("A1:A250")
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

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

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

End Sub

Set rng = wks.Range("A1:A250")
For Each c In rng

Not a big deal but this gets processed every iteration of the loop. You
don't need the 'rng' var...

For Each c In wks.Range("A1:A250")

...since it's the same for every sheet.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion