VBA Help- Need to create VBA Function from worksheet

[scotty1978]

This is one problem I haven’t had much success with by searching the internet. Was hoping someone could assist. I have an excel worksheet that helps me to calculate insurance reimbursement estimates on a daily basis. The goals is to be able to plug in a few data points, hit compute and have it all completed for me. So far my sheet is set up with the data points I need.

Estimated Rates based on Provider.
Deductible Amounts.
Coinsurance Amounts and Percentages.

Here is where things get a little confusing. My sheet calculates how many days until deductible is met, and then how many days until the coinsurance is met. But I need to manually input numbers on the right side in the green box.

In this example, the deductible is met on Day 7, and because of that some of the coinsurance is due on Day 7 as well. The yellow box that says Day 7 means the patient owes the $125 and Insurance responsible for the $25. Then it’s a 50/50 split because coinsurance. Patient pays 50% and Insurance Pays 50% until Day 21. Then insurance responsible for the rest of the days of treatments.

I’m wondering if there is a way to write a code in vba that correlates with the days and rates so that everything comes out the way the example shows instead of needing to manually input. Is this even possible? Thanks in advance.

 

[mse330]

Hi scotty1978,

Working without an actual dataset (images only) is difficult but try the below code ...

Sub test()

'clear old data
[J4].Resize(1000, 2).ClearContents

'1- patient
[J4].Resize([H9] - 1) = [C4]

'2- deductible
[J4].Offset([H9] - 1).Resize(, 2) = [transpose(H10:H11)]

'3- coinsurance
[J4].Offset([H9]).Resize([H17] - 1 - [H9], 2) = [C21:D21].Value

'4- insurance
[J4].Offset([H17] - 1).Offset(, 1).Resize(20) = [H19]

End Sub

 

[scotty1978]

Hi scotty1978,

Working without an actual dataset (images only) is difficult but try the below code ...

Sub test()

'clear old data
[J4].Resize(1000, 2).ClearContents

'1- patient
[J4].Resize([H9] - 1) = [C4]

'2- deductible
[J4].Offset([H9] - 1).Resize(, 2) = [transpose(H10:H11)]

'3- coinsurance
[J4].Offset([H9]).Resize([H17] - 1 - [H9], 2) = [C21:D21].Value

'4- insurance
[J4].Offset([H17] - 1).Offset(, 1).Resize(20) = [H19]

End Sub

Wow, truly incredible work. This is working with a couple exceptions. As the variables change, sometimes it doesn't work. For instance when the daily rate gets higher, I sometimes get a run-time error. And sometimes when the rates change it works till it gets to the day in H18:H19, it doesn't convert over properly on the patient side, the day 5 insurance rate doesn't covert to full daily rate in day 6, 7, 8, etc. trying to attach pictures, but keep saying file too large. Is there a way to send you the actual sheet? I would gladly do that. getting this working perfectly would be a tremendous help to me and my staff. Thanks again.

 

[mse330]

You may use google, drop box or any other sharing platform to upload a sample excel file of which we can help better.

 

[scotty1978]

You may use google, drop box or any other sharing platform to upload a sample excel file of which we can help better.

Insurance Spreadsheet


Alright, link posted above. Thank you!

 

[mse330]

I couldn't view/download the file. It says that I need to get access. Can you ensure to make the file available for public ?

 

[scotty1978]

I couldn't view/download the file. It says that I need to get access. Can you ensure to make the file available for public ?

Insurance Spreadsheet

Sorry about that. Hopefully the link works now.

 

[mse330]

No worries ... I downloaded the file successfully. Which TEST # does the macro fail ?

 

[mse330]

Ok, try below code on sheet object i.e. sheet4 in your sample file where the macro will execute once you change the selection in cell C3 ... Just one note, I have hardcoded the # of days to 20 in my last line of code of the 'insurance' but it should extend to how many days or what's the logic/cell to look at ?

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, [C3]) Is Nothing Then
   'clear old data
   [J4].Resize(1000, 2).ClearContents
   
   '1- patient
   [J4].Resize(IIf([H9] - 1 < 1, 1, [H9] - 1)) = [C4]
   
   '2- deductible
   [J4].Offset(IIf([H9] - 1 < 1, 1, [H9] - 1)).Resize(, 2) = [transpose(H10:H11)]
   
   '3- coinsurance
   [J4].Offset([H9]).Resize(IIf([H17] - 1 - [H9] < 1, 1, [H17] - 1 - [H9]), 2) = [C21:D21].Value
   
   '4- insurance
   [J4].Offset(IIf([H17] - 1 < 1, 1, [H17] - 1)).Offset(, 1).Resize(20) = [H19]
End If

End Sub

 

[scotty1978]

So to answer your last question, the logic is to get an estimate of 30 days or treatment units.
Thank you for adding the auto refresh when changing cell C3, that works great.
The only issue I'm still seeing is likely because of all the changing variables. C3 is not the only variable that changes. The Remaining Deductible (D7), Remaining OOP (D15), and Co-insurance Share % (C21 and D1), all change base on patients insurance policy.

As is, it seems to be working great on some examples and a little off on other examples.
With all the variables I've put on published sheet, Remaining Deductible-D7= $2,000. Remaining OOP-D15= $3000 and Coinsurance 50% and 50% (C21 and D21). If you look at Test 1 which makes the rate on C3= $800, it works perfectly.

If you switch to Test 2 where C3= $434.90, Day 5 in H9 coverts perfectly, but if you look at Day 10 in H17 has a remaining $42.95 on the patient side that's not showing in J13. In addition, you can see the figures on the right of J and K columns (Column L) that adds each line to test out if the full daily rate in C3 is met. The goal is for those daily rates to equal the rate in C3. In this example the $434.90. The full daily rate is the insurance responsibility (Column K) = C3, once the full OOP by patient is met. In this example Day 10. The full $434.90 should fill out the rest of the cells. Instead of $434.90 it still shows $391.95 through the rest of the days up to the 20 you hardcoded too.

If you play around with the Tests (Daily Rate) in C3 you'll see this randomly happen. And on many examples once we get past the Day in H17, Column K doesnt pick up full daily rate, the rate in H19 stays constant.

I hope this all makes sense. You seem to get the concept of the sheet because some examples are working perfectly as mentioned, but if you need further explanation or something is missing in the logic on your end, let me know and I'll do my best explain. The work you've done so far is nothing short of magical to me. Thanks again.