Excel Functions to VBA

Capture.JPG
I have an Excel tab "RawData" with the table - see attached screen snip.

Column 35 has this function:

=IF(RC[-26]<0,IF(RC[-26]>=-7,"Late 0-7 Days",IF(RC[-26]>=-30,"Late 8-30 Days",IF(RC[-26]>=-60,"Late 31-60 Days",IF(RC[-26]>=-90,"Late 61 to 90 Days","Late Over 90 Days")))))

Columns 36 to 40:

=IF(RC35="Late 0-7 Days",1,0)

=IF(RC35="Late 8-30 Days",1,0)

=IF(RC35="Late 31-60 Days",1,0)

=IF(RC35="Late 61 to 90 Days",1,0)

=IF(RC35="Late Over 90 Days",1,0)

I need to build a Sub that will do what Functions do in the above.

Can this be done in VBA?

Thanks!
Mich MichBI LeadAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
MIch

What exactly is it you need VBA to do?

Do you want code to put the formulas in columns 36-40?

Or perhaps code that will go down column 35 and instead of placing the formulas in the columns put the results instead?
1
Mich MichBI LeadAuthor Commented:
Norie, I need both.
I guess, first to have Column 35 calculations done by VBA and then respectively calculations done in 36-40. Thank you.
0
NorieAnalyst Assistant Commented:
Mich

Could you upload a sample workbook with some dummy data?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

aikimarkCommented:
This seems the simplest solution, using the Switch() function
Function Q_29069731(ByVal parmDiff) As String
    If parmDiff >= 0 Then
        Q_29069731 = vbNullString
    Else
        Q_29069731 = Switch(parmDiff >= -7, "Late 0-7 Days", parmDiff >= -30, "Late 8-30 Days", parmDiff >= -60, "Late 31-60 Days", parmDiff >= -90, "Late 61 to 90 Days", parmDiff < -90, "Late Over 90 Days")
    End If
End Function

Open in new window

0
aikimarkCommented:
This might be a bit easier to read
Function Q_29069731(ByVal parmDiff) As String
    If parmDiff >= 0 Then
        Q_29069731 = vbNullString
    Else
        Q_29069731 = Switch(parmDiff >= -7, "Late 0-7 Days", _
                            parmDiff >= -30, "Late 8-30 Days", _
                            parmDiff >= -60, "Late 31-60 Days", _
                            parmDiff >= -90, "Late 61 to 90 Days", _
                            parmDiff < -90, "Late Over 90 Days")
    End If
End Function

Open in new window

0
aikimarkCommented:

Testing results:

?Q_29069731(-4)
Late 0-7 Days
?Q_29069731(-14)
Late 8-30 Days
?Q_29069731(-42)
Late 31-60 Days
?Q_29069731(-4242)
Late Over 90 Days
?Q_29069731(42)

'end of tests

Open in new window

0
aikimarkCommented:
This is a solution using the Select Case statement:
Function Q_29069731a(ByVal parmDiff) As String
    Select Case parmDiff
        Case Is >= 0
            Q_29069731a = vbNullString
        Case Is >= -7
            Q_29069731a = "Late 0-7 Days"
        Case Is >= -30
            Q_29069731a = "Late 8-30 Days"
        Case Is >= -60
            Q_29069731a = "Late 31-60 Days"
        Case Is >= -90
            Q_29069731a = "Late 61 to 90 Days"
        Case Else
            Q_29069731a = "Late Over 90 Days"
    End Select
End Function

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mich MichBI LeadAuthor Commented:
aikimark, so for the value in that calculated column I just call Q_29069731a function?
0
aikimarkCommented:
Yes.  You can invoke the function just like any other (Excel Intrinsic) function.
0
Mich MichBI LeadAuthor Commented:
parmDiff - is a variable for my column 9 (column called "Late")? Correct?
0
aikimarkCommented:
yes.
0
aikimarkCommented:
@Mich

If you know the expected distribution of differences, you can improve the performance of the function.
Example:
In this example, the Case clause conditions are written to be executed in any order.  The hypothetical case for this example is that most of the differences will be 31-60 days late, followed by 8-30 days late, etc.
Function Q_29069731a(ByVal parmDiff) As String
    Select Case parmDiff
        Case -60 To -31
            Q_29069731a = "Late 31-60 Days"
        Case -30 To -8
            Q_29069731a = "Late 8-30 Days"
        Case -90 To -61
            Q_29069731a = "Late 61 to 90 Days"
        Case Is < -90
            Q_29069731a = "Late Over 90 Days"
        Case Is >= 0
            Q_29069731a = vbNullString
        Case -7 To -1
            Q_29069731a = "Late 0-7 Days"
    End Select
End Function

Open in new window

1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.