How can you test multiple counters for a value of 1 or more programatically?
How can you test multiple counters for a value of 1 or more programatically?
Peace of mind is found in some of the strangest places.
How do you define multiple counters.Originally Posted by austenr
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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
Peace of mind is found in some of the strangest places.
Can you post some spreadsheet data to test on?
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
here
Peace of mind is found in some of the strangest places.
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
totally confused... Example please
Peace of mind is found in some of the strangest places.
Sorry Austen,
I hadn't seen you data when I posted that solution.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
so i canot do it that way?
Peace of mind is found in some of the strangest places.
What is tempss?
Range("G").Value = tempss
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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
Peace of mind is found in some of the strangest places.
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.
Last edited by mdmackillop; 05-10-2005 at 12:46 PM. Reason: Corrected file attached
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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.
Peace of mind is found in some of the strangest places.
revised file
Peace of mind is found in some of the strangest places.
Grass cut!
I hadn't reset TempMem.
If .Value <> Tempss Then TempFam = 0 TempMem = "" End If
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Not sure what you mean..
Peace of mind is found in some of the strangest places.
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.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
If I sorted both columns with the primary being G and secondary I would that work?
Peace of mind is found in some of the strangest places.
Yes, that would do it.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'