PDA

View Full Version : [SOLVED] Dates In VBA



zoom38
04-12-2006, 02:39 PM
I cant get the following module to work. It doesnt seem to recognize the date when listed as "03/22/200" & myyr but does when I do the whole thing as "03/22/2007". The value of myyr is 7. Any ideas??


Sub Dashes(myyr)
'Unlock Top Row, Put In Dashes and Lock Cells With Dashes
Cells.Range("b8:n8").Locked = False
If Cells(8, 1).Value = "3/22/200" & myyr Then
Cells(8, 2) = "-"
Cells(8, 3) = "-"
Cells(8, 4) = "-"
Cells(8, 5) = "-"
Cells(8, 6) = "-"
'Cells.Range("b8:m8").Locked = True
End If
Cells(4, 4) = "3/22/200" & myyr + 1
End Sub


Thanks
Gary

Ken Puls
04-12-2006, 03:18 PM
Hi Gary,

Maybe try using Datepart:

Sub test()
If DatePart("d", Selection) = 22 And DatePart("m", Selection) = 3 Then
MsgBox "Looks like a March 22 of some year to me!"
End If
End Sub

Let me know if you need help adapting it.

zoom38
04-12-2006, 03:48 PM
Thanks Ken but of course I can't figure it out. This is what I have:


Sub Dashes()
'Unlock Top Row, Put In Dashes and Lock Cells With Dashes
Cells.Range("b8:n8").Locked = False
If DatePart("d", "a8") = 22 And DatePart("m", "a8") = 3 Then
Cells.Range("b8:f8") = "-"
Cells.Range("b8:f8").Locked = True
End If
End Sub


One good thing is that Im not receiving error messages. I tried adding the year but it still didn't work. What am I doing wrong here? I still can't figure it out even by looking at excel help for DatePart.

Gary

Ken Puls
04-12-2006, 03:52 PM
Almost... try this:


Sub Dashes()
'Unlock Top Row, Put In Dashes and Lock Cells With Dashes
Cells.Range("b8:n8").Locked = False
If DatePart("d", Range("a8")) = 22 And DatePart("m", range("a8")) = 3 Then
Cells.Range("b8:f8") = "-"
Cells.Range("b8:f8").Locked = True
End If
End Sub

Ken Puls
04-12-2006, 03:54 PM
Just as a followup... I usually try to make sure I explicity declare my ranges to a worksheet. I just feel more comfortable doing that, as I know exactly where they are then.


Sub Dashes()
'Unlock Top Row, Put In Dashes and Lock Cells With Dashes
With ActiveSheet
.Range("b8:n8").Locked = False
If DatePart("d", .Range("a8")) = 22 And DatePart("m", .range("a8")) = 3 Then
.Range("b8:f8") = "-"
.Range("b8:f8").Locked = True
End If
End With
End Sub

HTH,

zoom38
04-12-2006, 04:40 PM
Thanks Ken that worked awesome.
Please mark this one solved.

Thanks
Gary

Ken Puls
04-12-2006, 05:01 PM
Great stuff. Done! :D

Cyberdude
04-13-2006, 07:22 PM
I can't put my finger on it, but it looks to me like the comparison you are doing isn't general enough, and might get you into trouble if you use a different date value.

Suppose the date happens to be, say, March 4th, and cells(8,1) contains "03/04/2007". How would you write your reference literal? It can be written as "03/04/2007", "3/4/2007", "03/4/2007", "3/04/2007", "3/4/07", etc.

From your example, I assume that all dates are written as text strings instead of type date. If that's the case, you should probably convert both sides of the compare into type date:
If DateValue(Cells(8, 1).Value) = DateValue("3/22/" & myyr) Then ...
This would make it immune to the way the text is written.
Just a thought.

zoom38
04-13-2006, 10:41 PM
Actually the cell contains a date formula that is not user entered. Thanks for the tip.

Gary