Change multiple IF Formula to VBA Select Case

[blbat]

I have this formula that checks Dates in Column I:

=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:

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

 

[DanteAmor]

Review the following example and try to adapt it to your information.
Note: the dates in the example are in dd/mm/yyyy

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

 

[blbat]

Dante,
Thanks, that kicked my Brain into gear.

here's what I finally got to work:

(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

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Norie]

Instead of using the conversion function CDate why not use the DateSerial function?

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

 

[DanteAmor]

It is a good idea:

Instead of using the conversion function CDate why not use the DateSerial function?

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

 

[blbat]

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

 

[Norie]

I wasn't really thinking about the performance, more to do with avoiding day/month ambiguity.

 

[blbat]

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