Inaccurate count with code

  • Thread starter Thread starter Howard
  • Start date Start date
H

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.

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 said:
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
Click to expand...

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.
Click to expand...

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

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

Next wks
Click to expand...



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



For Each c In wks.rng
Click to expand...

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")
Click to expand...


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
Click to expand...

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
Click to expand...


ranage ("G1") on what sheet ?

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




















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.
















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




















ranage ("G1") on what sheet ?



a) active sheet?

b) specific sheet. in that case use worksheets("name").range
Click to expand...


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
Click to expand...

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
 
Back
Top