How to get the first day of the previous month...

[R Tanner]

Hi,

I have a variable called CDAT. This is a date that corresponds to a
cell reference. In my code, I want to [continually] modify this
variable to give me the first day of the previous month. i.e. I want
to modify it to equal 07/01/2008, then modify it to equal 06/01/2008
then 05/01/2008 etc.

I have been playing around with the eomonth worksheet function in my
code, but as weird as it is, it does not give me the same response as
when I put it in a cell. I actually think it might be that I am using
the 'day' formula in my code and expecting to get the same response as
I would in worksheetfunction. This is what I have.

Sub Modify_Table(ByVal cdat As Date)
Dim cdat As Date


cdat = Sheets("Data Layout").range("B35").Value
MsgBox cdat
cdat = cdat - Day(15)
MsgBox cdat
cdat = Application.WorksheetFunction.EoMonth(cdat, -2)
cdat = cdat + Day(1)
MsgBox cdat



ActiveSheet.ListObjects("WDT").range.AutoFilter field:=1,
Criteria1:=">=" & cdat, Operator:=xlExpression


End Sub

Any help is much appreciated...

 

[R Tanner]

Nevermind...I figured it out...I used the following code, FYI.

If anyone knows a more efficient way to code this, please let me know
however! At present anything that works is wonderful.


Dim cdat As Date
Dim D As Integer



cdat = Sheets("Data Layout").range("B35").Value
MsgBox cdat
cdat = cdat - Day(15)
D = Day(cdat)
cdat = cdat - Day(D)

MsgBox cdat

 

[Gary Keramidas]

nit exactly sure what you want, but give this a try

Sub test()
Dim cdat As Date
Dim D As Integer
cdat = Sheets("Data Layout").Range("B35").Value
MsgBox cdat
cdat = DateSerial(Year(cdat), Month(cdat) - 1, Day(0))
D = Day(cdat)
cdat = cdat - Day(D)
MsgBox cdat
End Sub