Converting "Select ... Case" to "If" format

jonny · May 19th 2019

The below VBA code scores supplier, based on its response time, please your help to convert it to "IF" function format.

Code

Private Sub Score()
'Scoring Supplier
    Response_Time = Today() - Completed_Date
    Select Case True
        Case Response_Time <= 7
            Score = 4
        Case Response_Time > 7 And Response_Time <= 14
            Score = 3
        Case Response_Time > 14 And Response_Time <= 21
            Score = 2
        Case Response_Time > 21
            Score = 1
    End Select
End Sub

Display More

Thank you

Logit · May 19th 2019

Code

If Today() - Completed_Date <= 7 then
     Score = 4
elseif Today() - Completed_Date > 7 and Today() - Completed_Date <= 14 then
     Score = 3
elseif Today() - Completed_Date > 14 and Today() - Completed_Date <= 21 then
     Score = 2
elseif Today() - Completed_Date > 21 then
     Score = 1
end if

jonny · May 19th 2019

By "if" i meant to existing Excel function. As it's less readable, I've got lost there..

Logit · May 19th 2019

Code

Option Explicit


Private Sub Score()
'Scoring Supplier


If Today() - Completed_Date <= 7 Then
    Score = 4
ElseIf Today() - Completed_Date > 7 And Today() - Completed_Date <= 14 Then
    Score = 3
ElseIf Today() - Completed_Date > 14 And Today() - Completed_Date <= 21 Then
    Score = 2
ElseIf Today() - Completed_Date > 21 Then
    Score = 1
End If


End Sub

Display More

jonny · May 19th 2019

If function, not VBA

jonny · May 20th 2019

**KjBox** · May 20th 2019

Assuming Row 1 is a header row and column J contains the "Completed Dates", try this in row 2 of the column where you want the results and copy down

=IF(TODAY()-J2<=7,4,IF(TODAY()-J2<=14,3,IF(TODAY()-J2<=21,2,1)))

Change each "J" in the formula to your actual "Completed Dates" column.

jonny · May 20th 2019

Exactly what I was missing for. Thank you!

**KjBox** · May 20th 2019

You're welcome

Converting "Select ... Case" to "If" format

Participate now!

Share