PDA

View Full Version : [SOLVED] Testing multipe counters



austenr
05-10-2005, 09:29 AM
How can you test multiple counters for a value of 1 or more programatically?

Bob Phillips
05-10-2005, 09:42 AM
How can you test multiple counters for a value of 1 or more programatically?

How do you define multiple counters.

austenr
05-10-2005, 10:12 AM
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
05-10-2005, 10:19 AM
Can you post some spreadsheet data to test on?

austenr
05-10-2005, 10:27 AM
here

mdmackillop
05-10-2005, 10:27 AM
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
05-10-2005, 10:30 AM
totally confused... Example please

:dunno

mdmackillop
05-10-2005, 10:32 AM
Sorry Austen,
I hadn't seen you data when I posted that solution.

austenr
05-10-2005, 10:33 AM
so i canot do it that way?

mdmackillop
05-10-2005, 10:36 AM
What is tempss?
Range("G").Value = tempss

austenr
05-10-2005, 10:40 AM
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
05-10-2005, 11:34 AM
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
05-10-2005, 11:46 AM
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
05-10-2005, 12:05 PM
revised file

mdmackillop
05-10-2005, 12:33 PM
Grass cut!
I hadn't reset TempMem.


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

mdmackillop
05-10-2005, 12:47 PM
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
05-10-2005, 03:56 PM
Not sure what you mean..

mdmackillop
05-10-2005, 04:03 PM
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
05-10-2005, 04:51 PM
If I sorted both columns with the primary being G and secondary I would that work?

mdmackillop
05-11-2005, 12:09 AM
Yes, that would do it.

austenr
05-11-2005, 06:31 AM
Thanks for all your help...Solved