PDA

View Full Version : How to Allocate ER Credit with VBA



vb4betrm
01-26-2016, 03:41 PM
Greetings vbaexpress coders!

I think there may be a better way to structure my code for logic and efficiency. So I welcome feedback to improve it.
It seems to accomplish most of the below requirements. However, I need help on resolving a couple of issues.

Issues:
How do I loop by ID to determine if the ID has "DeclineMedical"? Will need this to apply requirement #1.
How do I loop by ID to determine if the ID has "Medical"? Will need this to apply requirement #2.

As shown in the attached example, there are two with Medical, but it should not calculate amounts (in red) for New EECost and New ERCost.

Requirements:
1. If ID has "DeclineMedical" Plan and has other Plan(s) with a Tax value of "Y" and Code is not "017", allocate the employer paid credit of 41.66…
A. calculate New EECost by reducing original EECost by the 41.66 credit
* if difference <= 0, New EECost amount is 0.00
* if difference > 0, New EECost is difference of original EECost less 41.66
* then move to next Plan having a Tax value of "Y" and allocate any remaining credit (if any)
B. calculate New ERCost by reducing original EECost by the 41.66 credit
* if difference >= 0, New ERCost is 41.66
* if difference < 0, New ER cost is original EECost
* then move to next Plan having a Tax value of "Y" and allocate any remaining credit (if any)
2. If ID has Medical, just copy all original EECost and all original ER Cost values as they are into New EECost and New ERCost columns.


Thank you very much!

SamT
01-27-2016, 02:38 AM
Reading the code, it appears that the conditions are

Where each block of IDs is a Patient


For each Patient

If Not Has Other Plan Then Next patient

If Not Has Code 17 then Next Patient

For each line in Patient, If Code = 17

If EECost <= erPaidCredit

If Plan is Medical
ElseIF Plan is DeclineMedical
Else do other stuff


Else

If Plan = Medical
ElseIF Plan = DeclineMedical
Else do other stuff


End If


Next Line


End If


End If


Next Patient

So what if the ID has another Plan and what if the code is 17

SamT
01-27-2016, 03:03 AM
This code assigns each block of identical IDs to the Range Patient

Option Explicit

Sub VBAX_SamT_Basic_Allocate_ER_Credit()
Dim PatientBlock As Range
Dim TopCell As Range
Dim BottomCell As Range
Dim NextID As Variant

NextID = Range("A2")
Set BottomCell = Range("A1")

Do While NextID <> ""

Set TopCell = Columns(1).Find(What:=NextID, After:=BottomCell, SearchDirection:=xlNext)
Set BottomCell = Columns(1).Find(What:=NextID, After:=BottomCell, SearchDirection:=xlPrevious)
NextID = BottomCell.Offset(1)
Set PatientBlock = Range(TopCell, BottomCell.Offset(, 7))

'Call Sub to Process all rows in Patient range block
ProcessPatient Patient
Loop
End Sub

Sub ProcessPatient(PatientBlock As Range)
'This sub only has to work with one ID at a time
'Declare Variables
'Loop thru each row in PatientBlock Range
End Sub

snb
01-27-2016, 03:52 AM
or:


Sub M_snb()
sn = Sheet11.Cells(1).CurrentRegion
sp = Filter([transpose(if(F1:F1000="DeclineMedical",A1:A1000,"~"))], "~", False)

For Each it In sp
For j = 1 To UBound(sn)
If (sn(j, 1) & sn(j, 5) = it & "Y") * (sn(j, 2) <> "017") Then sn(j, 8) = sn(j, 3) ' and all other calculations
Next
Next

End Sub

vb4betrm
01-28-2016, 10:23 AM
SamT and snb, I really appreciate very much your quick suggestions, but I'm not sure how to build them into the code I'm using.

I will try to explain what needs to be done in more detail by providing four examples. Please pardon me if my explanations may appear to be long-winded, but I hope they make sense.

Requirement #1 - determine if data “within a group” of unique IDs has DeclineMedical, is not Code “017” and has a Plan with Tax value of “Y” in order to apply the credit allocation.

1. Loop through column “A” (ID) and determine a range of unique IDs and its related block of data under Code, EECost, ERCost, Tax and Plan.

2. Search under column “F” (Plan) to see if any of the cells within the range of unique IDs has a value of “DeclineMedical”.

3. If “DeclineMedical” is found within the range of unique IDs and IF…

* the Plan does not have a Code of “017”
AND
* the Plan has a Tax value of “Y”

THEN execute the routine that allocates the 41.66 credit which calculates new amounts for column “G” (New EECost) and column “H” (New ERCost).

Example 1: unique ID 333333 has four rows of data and has “DeclineMedical” in cell “F13”. In this scenario, some of the amounts for ID 333333 under EECost and ERCost satisfy additional criteria (not Code “017” and Tax is “Y”), so new amounts need to be calculated for these to place into New EECost and New ERCost respectively.

Bridge - has EECost of 15.60 and ERCost of 0.00. The new amounts are calculated by subtracting 41.66 from the EECost of 15.50, which results in a negative amount. So the New EECost is 0.00 and the New ERCost is 15.60. And there is an unused credit amount of 26.06 (41.66 – 15.60 = 26.06) which may be used to reduce the next qualifying Plan with a Tax value of “Y”. In this example, the next Plan that qualifies for the credit allocation is Vision.

Vision - has EECost of 20.36 and ERCost of 0.00. The new amounts are calculated by subtracting the unused credit of 26.06 from the EECost of 20.36, which results in a negative amount. So the New EECost is 0.00 and the New ERCost is 20.36. Since there are no other Plans that meet the qualifying criteria, we move on to the next unique ID.

Example 2: unique ID 444444 has six rows of data and has “DeclineMedical” in cell “F19” and has Code “017” in cell “B14”. So in this scenario, only one of the amounts for ID 444444 under EECost and ERCost satisfy additional criteria (not Code “017” and Tax is “Y”), thus new amounts need to be calculated for these to place into New EECost and New ERCost respectively.

FSA – although this Plan has a Tax value of “Y”, it’s by-passed for the credit allocation routine because it has a Code of “017. So for this one just copy the EECost of 212.50 and ERCost of 0.00 into the New EECost and New ERCost columns respectively.

Accident - has EECost of 39.90 and ERCost of 0.00. The new amounts are calculated by subtracting 41.66 from the EECost of 39.90, which results in a negative amount. So the New EECost is 0.00 and the New ERCost is 39.90. Since there are no other Plans that meet the qualifying criteria, we move on to the next unique ID

Example 3: unique ID 555555 has three rows of data and has “DeclineMedical” in cell “F22”. In this scenario, only one of the amounts for ID 555555 under EECost and ERCost satisfy additional criteria (not Code “017” and Tax is “Y”), so new amounts need to be calculated for these to place into New EECost and New ERCost respectively.

Dental - has EECost of 53.70 and ERCost of 0.00. The new amounts are calculated by subtracting 41.66 from the EECost of 53.70, which results in an amount greater than or equal to zero (53.70 – 41.66 = 12.04). So the New EECost is 12.04 and the New ERCost is 41.66. Since there are no other Plans that meet the qualifying criteria, we move on to the next unique ID.

Basically, the three examples above indicate what needs to be done if data “within a group” of unique IDs has DeclineMedical, is not Code “017” and has a Plan with Tax value of “Y” in order to apply the credit allocation.

I will now explain in Example 4 what needs to be done when data “within a group” of unique IDs has Medical in order to by-pass the credit allocation.

Requirement #2 - determine if data “within a group” of unique IDs has Medical to by-pass the credit allocation.

1. Loop through column “A” (ID) and determine a range of unique IDs and its related block of data under Code, EECost, ERCost, Tax and Plan.

2. Search under column “F” (Plan) to see if any of the cells within the range of unique IDs has a value of “Medical”.

3. If “Medical” is found within the range of unique IDs, then just copy amounts from column “C” (EECost) and column “D” (ERCost) into column “G” (New EECost) and column “H” (New ERCost).

Example 4: unique ID 222222 has eight rows of data and has “Medical” in cell “F3”, so in this scenario, all that needs to happen is all amounts for ID 222222 under EECost and ERCost need to be copied under New EECost and New ERCost respectively. So the allocation of the 41.66 credit should be by-passed in this instance.

Issue with Example 4: As you can see, my code is not by-passing rows for unique ID 222222 and goes through the routine that allocates the 41.66 credit which produces some incorrect amounts for New EECost and New ERCost shown in red because they satisfy additional criteria (not Code “017” and Tax is “Y”). However, this unique ID 222222 has Medical so the correct amounts should be:

Cancer - New EECost of 41.00 and New ERCost of 0.00 instead of 0.00 and 41.00

Dental - New EECost of 53.70 and New ERCost of 0.00 instead of 53.04 and 0.66

Note that a similar issue as indicated above for Example 4 also happens to unique ID 999999.

I have provided a revised attachment.


15309

SamT
01-28-2016, 02:48 PM
I only have 2 questions
So what if the ID has another Plan and what if the code is 17
And no, I did not read your new 50 page dissertation with the 8 by 10 glossy photos.

Not going to, Because, at this time I only have 2 questions
So what if the ID has another Plan and what if the code is 17

vb4betrm
01-29-2016, 08:55 AM
SamT, sorry for long story.

1. If the ID has another Plan. This depends on if ID has Medical or has Decline Medical...

* if ID has Medical simply by-pass allocation of 41.66 ER Credit for all of the ID's plans (which includes Other plans)
* if ID has Decline Medical, loop through all ID's plans (which includes Other plans) and allocate the 41.66 ER Credit only if the plan has Tax = "Y" and is not code = "017"

2. If any plan has a code of "017" by-pass allocation of 41.66 ER Credit.


Thanks for taking time to help!

SamT
01-29-2016, 01:08 PM
I just spent some time trying to figure things out. Then I had to answer Nature's call and realized the we are talking about Rules.

Code operates by Rules. Even the most complex Rules can be described use a few simple statements. The Base of these statements is If this then that.

All IDs either have Medical or they don't. All Plans either have Code = 17 or they don't. All plans either have Tax, or they don't.

In all the examples you have written, you have been unconsciously applying these Rules. We Coders don't need Examples, we need the rules.

The Rules should be written Thusly

If Patient has Medical Then
'Do This
Else 'Patient doesn't have Medical
If Plan has Tax Then
If plan Code 17 Then
'Do this
Else ' Plan doesn't have Code 17
'Do this
End If ' Has/doesn't has Code 17
Else 'Doesn't have Tax
If Code = 17 Then
'Do this
Else ' Doesn't have 17
'Do this
End If ' Has/doesn't has Code 17
End If ' Has/doesn't have tax
End If 'has/doesn't have medical