Change multiple IF Formula to VBA Select Case

blbat

Active Member
Joined
Mar 24, 2010
Messages
338
Office Version
  1. 2016
  2. 2013
I have this formula that checks Dates in Column I:

Code:
=IF($I2<=DATE(2018,10,7),"Base", IF(AND($I2>=DATE(2018,10,8),$I2<=DATE(2018,10,29), "Interest",IF(AND($I2>DATE(2018,10,29),$I2<=DATE(2018,12,17), "Advance", IF($I2>=DATE(2018,12,17), "Sustain", "ERROR!"))))

I want to insert a "Select Case" into an existing bit of Code I have, but I don't know all the pitfalls...like checking if Column I is actually a date, or blank or whatever...

I scratched out a basic framework, then realized I really don't know how to proceed.
Can I use an existing "LastRow" variable instead of looping through Column I?

this is sort of what I was looking for:
Code:
Select Case Phases

Dim Result as String

Case is <= 10/7/2018
	Result = "Base"
Case is >= 10/8/2018 AND <=10/29/2018
	Result = "Interest"
Case is > 10/29/2018 AND <12/17/2018
	Result = "Advance"
Case is >= 12/17/2018
	Result = "Sustain"
Case Else
	Result = "ERROR"

End Select
the result would be inserted into the corresponding cell in column J

thanks for any help, or pointers

blbat
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Review the following example and try to adapt it to your information.
Note: the dates in the example are in dd/mm/yyyy

Code:
Sub test()
    phases = CDate("25/12/2018")
    Dim Result As String
    
    Select Case phases
        Case Is <= CDate("07/10/2018")
            Result = "Base"
        Case CDate("08/10/2018") To CDate("29/10/2018")
            Result = "Interest"
        Case CDate("29/10/2018") To CDate("17/12/2018")
            Result = "Advance"
        Case Is >= CDate("17/12/2018")
            Result = "Sustain"
        Case Else
            Result = "ERROR"
    End Select
    
    LastRow = Range("I" & Rows.Count).End(xlUp).Offset(1).Row
    Cells(LastRow, "I").Value = Result
End Sub
 
Upvote 0
Dante,
Thanks, that kicked my Brain into gear.

here's what I finally got to work:
Code:
(Dim'd the variables above these lines)

Set RngIw = mySheetW.range("I2:I, & LastRow)

For Each RngIVal In RngIw.Cells

Select Case RngIVal.Value

Case is <= CDate("10/07/2018")
	Result = "Base"
Case CDate("10/08/2018") To CDate ("11/04/2018")
	Result = "Interest"
Case CDate("11/05/2018") To CDate ("12/16/2018")
	Result = "Advance"
Case Is >= CDate("12/17/2018")
	Result = "Sustain"
Case Else 
	Result = "ERROR!!"

End Select

RngIVal.Offset(0,1).Value = Result
Result = vbNullString

Next

End Sub
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0
Instead of using the conversion function CDate why not use the DateSerial function?
Code:
Select Case RngIVal.Value

Case is <= DateSerial(2018, 10, 7)                                     ' <=10/07/2018
	Result = "Base"
Case DateSerial(2018, 10, 8) To DateSerial(2018, 11, 4)       ' 10/08/2018 To 11/04/2018
	Result = "Interest"
Case DateSerial(2018, 11, 5 To DateSerial(2018, 12, 16)      ' 11/05/2018 To 12/16/2018
	Result = "Advance"
Case Is >= DateSerial(2018, 12, 17)                                   ' >=12/17/2018
	Result = "Sustain"
Case Else 
	Result = "ERROR!!"

End Select
 
Upvote 0
It is a good idea:

Instead of using the conversion function CDate why not use the DateSerial function?
Code:
Select Case RngIVal.Value

Case is <= DateSerial(2018, 10, 7)                                     ' <=10/07/2018
    Result = "Base"
Case DateSerial(2018, 10, 8) To DateSerial(2018, 11, 4)       ' 10/08/2018 To 11/04/2018
    Result = "Interest"
Case DateSerial(2018, 11, 5 To DateSerial(2018, 12, 16)      ' 11/05/2018 To 12/16/2018
    Result = "Advance"
Case Is >= DateSerial(2018, 12, 17)                                   ' >=12/17/2018
    Result = "Sustain"
Case Else 
    Result = "ERROR!!"

End Select
 
Upvote 0
Norie,

Thanks for jumping in on my thread...I always appreciate your input.

I didn't use DateSerial because I never even thought of it..../sigh.

I see by some basic research that DateSerial generally runs faster than CDate, so I will re-write my code tomorrow.

Thanks again!

blbat
 
Upvote 0
I wasn't really thinking about the performance, more to do with avoiding day/month ambiguity.:)
 
Upvote 0
Norie,

Change made, However, the record set I'm running this code on is fairly small- so the time to execute using DateSerial was not much faster than using CDate.

I like the use of DateSerial though, so I used it.

blbat
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top