Testing multipe counters

[austenr]

How can you test multiple counters for a value of 1 or more programatically?

[Bob Phillips]

How can you test multiple counters for a value of 1 or more programatically?

How do you define multiple counters.

[austenr]

Here is the whole routine. It is really ugly and probably does not produce the desired result (at least I can't get it to work). It is totally my work and really ugly. What it is supposed to do is, check row by row, and depending on if the value in column G has changed or not add to counters and do other things. Could someone please look it over. Thanks.

Option Explicit
 
Public Sub DetermineSameSocial()
' Checks to see if the value in column G is the same as the previous row
' If not Add to coverage types and clear temporary counters
' Else process row
Dim s, Row, LastRow, SELF, ECH, FAM, ESP, tempss, tempself, tempch, _
tempfam, tempesp, tempsp, tempech As Integer
Range("G:I").Select
Row = 2
For s = 1 To LastRow
Range("G").Value = tempss
If Range("G").Value > tempss Then
DetermineCov
ClearTmpCntrs
End If
' Add to counters
If Range("I").Value = "SELF" Then
tempself = tempself + 1
Else
If Range("I").Value = "CHILD" Then
tempch = tempch + 1
Else
If Range("I").Value = "SPOUSE" Then
tempsp = tempsp + 1
End If
End If
End If
Next s
MsgBox ("Self" + tempself)
End Sub
 
Public Sub DetermineCov()
Dim tempself, tempch, tempsp, tempfam, tempesp, tempech As Integer
' Determine self coverage
If tempself >= 1 And tempch = 0 And tempsp = 0 Then
tempself = 1
End If
'Determine Family Coverage
If tempself >= 1 And tempch >= 1 And tempsp >= 1 Then
tempfam = 1
End If
'Determine Employee Plus Spouse coverage
If tempself >= 1 And tempsp >= 1 And tempch = 0 Then
tempesp = 1
End If
'Determine Employee plus child Coverage
If tempself >= 1 And tempch >= 1 And tempsp = 0 Then
tempech = 1
End If
End Sub
 
Public Sub ClearTmpCntrs()
Dim tempself, tempch, tempsp As Integer
tempself = 0
tempch = 0
tempsp = 0
End Sub

[mdmackillop]

Can you post some spreadsheet data to test on?

[austenr]

here

[mdmackillop]

One way of doing this (not having seen your data) is the same as the inputbox method for buttons. You use a single variable and add a figure of 2^n for each option. You can then analyse this using a select case statement, for any combination of data. Whether this is practicable, depends upon your number of variables of course.

[austenr]

totally confused... Example please

[mdmackillop]

Sorry Austen,
I hadn't seen you data when I posted that solution.

[austenr]

so i canot do it that way?

[mdmackillop]

What is tempss?
Range("G").Value = tempss

[austenr]

I need a way to compare the value in G(row# which in the example file is column A) when I go to the next row. If it is not the same, then I need to do the decide coverage routine and clear the temp totals. HTH

[mdmackillop]

Hi Austen,
Have a look at the attached. Its a different methodology, and there are other ways to approch this. It's not 100% as you will see, but maybe you can iron out the bugs.
I've got to go and cut the grass!

Corrected file now attached.

[austenr]

Thanks. That works like you said. Can't figure out how it gets off track and then back on!! Your work is appreciated. I am attaching the file and is highlighted where it gets off track. If anyone else wants to take a crack at it.

[austenr]

revised file

[mdmackillop]

Grass cut!
I hadn't reset TempMem.

If .Value <> Tempss Then
TempFam = 0
TempMem = ""
End If

[mdmackillop]

Hi Austen,

I've tidied up and attached my corrected file to Post 12 above.
Please note that this code will not give the correct answer in Child values in a group are split, eg by Spouse or Self. If this is a possibility, let me know.

[austenr]

Not sure what you mean..

[mdmackillop]

In column I, Self, Child, Spouse, Child in one Family would give a different result from
Self, Spouse, Child, Child, because the code triggers on a Column I change (as well as column G) It depends upon your data entry etc. whether this might occur and be a problem.

[austenr]

If I sorted both columns with the primary being G and secondary I would that work?

[mdmackillop]

Yes, that would do it.