Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select Case in VBA not working
Your Format Function returns a string version of your date, so what you are
attempting to do is compare a string data type to a date/time data type, which this doesn't work out. Maybe you could try the following before you get to the start of your Select Case series. Dim Date1 as Date Date1 = Date Or better yet, sense the Date Function already contains the current date (Based on your PC date/time), why don't you just skip the variable all together and just compare the cell to the Date Function. -- Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000 "Colin" wrote in message m... This is going to be so simple compared to some of the posts I've read trying to track down an answer to my problem but I would be very grateful for a solution. I've actually achieved a lot with coding for all sorts of purposes but with no programming background there are some simple things that just defeat me. I?m trying to use VBA to delete any rows in a worksheet range which have a date in the future, while ignoring rows where the target cell contains other values ? in this case AA, REV, a date in the past, or is blank. The problematic section of code is as follows: Date1 = Format(Now, "dd/mm/yy") 'MsgBox "Date1: " & Date1 Do Until ActiveCell.Value = "" Select Case ActiveCell.Offset(0, 1).Value Case "AA", "REV", "", Is < Date1 Case Is Date1 ActiveCell.EntireRow.Delete Shift:=xlUp End Select ActiveCell.Offset(1, 0).Select Loop It does what I expect for cells with AA, REV, Blank and a date in the past i.e. nothing, but cells with a date in the future get picked as < Date1 when they are greater. (I separated out Case Is <Date1 at one point to try and work out what was going on). In my despair, I checked the values of Date1 and my target cell using MsgBox and both values are identical but the Case expression Is Date1 just doesn?t work and I don?t know why. I tried having a cell value of 11/09/03 i.e. equal to Date1 and using the code Case Is = Date1 to see if that would give me any clues but it just skips over this as well and doesn?t pick it up. I tried Case Date1 and then Case Else, but AA, REV etc get picked up as Date1. Some/most/all of you guys will find this embarrassingly easy to solve and no sort of challenge to your skills but it?s driving me insane and I would be really grateful for your help. Thanks. Colin |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select Case | Excel Discussion (Misc queries) | |||
Case Select | Excel Worksheet Functions | |||
Case without Select Case error problem | Excel Discussion (Misc queries) | |||
Select Case | Excel Discussion (Misc queries) | |||
Need help on Select Case | Excel Worksheet Functions |