Code Logic Help!

[NamssoB]

This is more of a coding logic problem than it is Excel (I think!), but since it's in Excel VBA and will be running from a Click even on a button we have inside of a Worksheet, I'm hoping for some help. Here is the deal:

1) User enters a number of minutes spent with a patient.
2) My code calculates the correct billing codes to use based on the time spent, in minutes.

Here is how it breaks down: (detailed grid also provided below, along with the code I've written so far):

There are only FOUR (4) codes, total. But they get appended/added onto each other as time progresses.
95978: Code for first hour
95978-52 (Modifier added if 30 minutes or less)

95979: Subsequent 30 minutes
95979-52: If only 15 minutes are used in the subsequent 30 minutes

For example:

00-30: 95978-52
31-60: 95978

61-75: 95978 + 95979-52
76-90: 95978 + 95979
91-105: 95978 + 95979 + 95979-52
106-120: 95978 + 95979 + 95979

121-135: 95978 + 95979 + 95979 + 95979-52
136-150: 95978 + 95979 + 95979 + 95979
151-165: 95978 + 95979 + 95979 + 95979 + 95979-52
166-180: 95978 + 95979 + 95979 + 95979 + 95979

And so on...my issue is, I can't hard code the specific time frames. I need to use an algorithm to fill in the codes. Can someone help? Below is the code I tried that "mostly" works up until about 90, then there are some major logic issues.

Private Sub GetBillingCode_Click()

    Dim sglTotalTime As Single
    
    Dim sglExtraTime As Single
    Dim intHours As Integer
    Dim intHalfHours As Integer
    
    Const strCodeBase As String = "95978"
    Const strCodeModifier As String = "-52"
    Const strCodeAdded As String = "95979"
    
    Dim strFinalCode As String
    
    sglTotalTime = Range("B3").Value
    sglExtraTime = (sglTotalTime Mod 60) / 60
    intHours = Int((sglTotalTime - sglExtraTime) / 60)
    
    strFinalCode = strCodeBase
    
    If sglTotalTime < 31 Then
        strFinalCode = strFinalCode & strCodeModifier
    End If
    
    If sglTotalTime > 60 Then

        strFinalCode = strFinalCode & " + " & strCodeAdded

        ' LOOP here for the number of hours there are?
        For x = 1 To intHours

            strFinalCode = strFinalCode & " + " & strCodeAdded

            ' First 15 minutes (0 to .25, 61-75) adds a modifier
            If (sglExtraTime > 0 And sglExtraTime <= 0.25) And (x = intHours) Then
                strFinalCode = strFinalCode & strCodeModifier

            ' Second 15 minutes (.25 to .50, 76-90) adds nothing, leave it alone!

            ' Last HALF (.5+, 91-120) adds another added code, so add it here
            ElseIf sglExtraTime > 0.5 Then
                strFinalCode = strFinalCode & " + " & strCodeAdded

                ' 3rd "quarter" (.50 to .75, 90-105) adds a modifier
                If sglExtraTime <= 0.75 And x = intHours Then
                    strFinalCode = strFinalCode & strCodeModifier
                End If
            Else
                'strFinalCode = strFinalCode & " + " & strCodeAdded
            End If
        Next x

        For x = 1 To intHours
            If x < intHours Then
                
    End If
    
    Range("G7").Value = strFinalCode

End Sub

 

[JLGWhiz]

Have you tried select case

 

[NamssoB]

Have you tried select case

Of course, and Ifs also. The issue is that those structures only work for limited (and hard coded) time frames. What if I want to allow 5 hours, 6 hours, 100 hours, etc? Changing the code every time is not practical. So my attempt was to design a looping structure that would simply append more codes on the end of the starting codes based on the amount of time. This would provide scalability to any length of time assuming the rules didn't change.

 

[igold]

Hi,

Does this do what you want...

Private Sub GetBillingCode_Click()


    Dim tim As Long, hrmult As Long, half As Long
    Dim x As Long, y As Long, xx As Long
    Dim billing As String
    
    tim = Range("A1")
    x = tim Mod 60
    If tim <= 30 Then
        billing = "95978-52"
        GoTo Show
    End If
    If tim > 30 And tim <= 60 Then
        billing = "95978"
        GoTo Show
    End If
    If tim > 60 Then hrmult = Int(tim / 60)
    billing = billing & "92578" & " + "
    tim = tim - 60
    half = Int(tim / 30)
    xx = tim Mod 30
    For y = 1 To half
        billing = billing & "95979" & " + "
    Next
    If xx > 0 And xx <= 15 Then billing = billing & "95979-52" & " + "
    If xx > 15 Then billing = billing & "95979" & " + "
    billing = Left(billing, Len(billing) - 3)
Show:
    MsgBox billing


End Sub

I hope this helps...

 

[igold]

Forgot to mention, please place your time in minutes in Cell A1.

 

[mikerickson]

Have you considered a VLOOKUP table (time to code) rather than deriving the code from the minutes. It will be easier to update when they change the billing codes, rather than rewriting a new derivation logic.

 

[NamssoB]

Hi,

Does this do what you want...
I hope this helps...

Yes, thank you! I actually was considering trying to use HALF hour increments to solve this, but couldn't think through the logic. Excellent. I did end up restructuring things a little (removed a couple unused lines of code, and replaced the GOTO statements with some nested IFs). Thank you!

Have you considered a VLOOKUP table (time to code) rather than deriving the code from the minutes. It will be easier to update when they change the billing codes, rather than rewriting a new derivation logic.

Yes, actually it's already working with a VLOOKUP table, but, it has a maximum limit. Anytime we need to adjust, we would need to update the table. Also, we're looking at porting this functionality into non-Excel platforms (Word, Web, iOS), so getting the code logic helps us do that. But thank you!

Final code I'm using:

Private Sub ZZGetBillingCode_Click()
    Dim lngTime As Long, lngHours As Long, lngHalfHours As Long
    Dim x As Long, y As Long, xx As Long
    Dim billing As String
    lngTime = Range("B3")
    x = lngTime Mod 60
    If lngTime <= 30 Then
        billing = "95978-52"
    Else
        If lngTime > 30 And lngTime <= 60 Then
            billing = "95978"
        Else
            billing = "92578" & " + "
            lngTime = lngTime - 60
            lngHalfHours = Int(lngTime / 30)
            xx = lngTime Mod 30
            For y = 1 To lngHalfHours
                billing = billing & "95979" & " + "
            Next
            If xx > 0 And xx <= 15 Then billing = billing & "95979-52" & " + "
            If xx > 15 Then billing = billing & "95979" & " + "
            billing = Left(billing, Len(billing) - 3)
        End If
    End If
    Range("G7").Value = billing
End Sub

 

[igold]

Great, I am glad I could help. I knew that there were some lines of code in there that would be useless to you when you applied it to your data, but I included them so you could see the results on a blank sheet.

I enjoyed figuring out the logic to get it to work.

Thanks for the feedback.