Change multiple IF Formula to VBA Select Case

blbat

Active Member
Joined
Mar 24, 2010
Messages
292
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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
 

blbat

Active Member
Joined
Mar 24, 2010
Messages
292
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,811
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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
 

blbat

Active Member
Joined
Mar 24, 2010
Messages
292

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
75,811
Office Version
  1. 365
Platform
  1. Windows
I wasn't really thinking about the performance, more to do with avoiding day/month ambiguity.:)
 

blbat

Active Member
Joined
Mar 24, 2010
Messages
292
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,624
Messages
5,523,968
Members
409,547
Latest member
AW2020

This Week's Hot Topics

Top