Formula For VBA

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Formula For VBA

Post by jstevens »

How does one state the following formula in code:

=DAY(DATE(M1,K1+1,0))

Where Cell M1 = 2012 and Cell K1 = 2

The result should return 29

I thought something like this should work but does not:
Msgbox DAY(DATE(Range("M1"),Range("K1")+1,0))


Thanks,
John
Regards,
John

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Formula For VBA

Post by HansV »

Some worksheet functions have a VBA equivalent with the same name, but others don't. The VBA equivalent of DAY is Day, but the VBA equivalent of DATE is DateSerial. (Date is the VBA equivalent of the TODAY function).

So try

MsgBox Day(DateSerial(Range("M1"), Range("K1") + 1, 0))
Best wishes,
Hans

jstevens
GoldLounger
Posts: 2618
Joined: 26 Jan 2010, 16:31
Location: Southern California

Re: Formula For VBA

Post by jstevens »

Thank you Hans...

John
Regards,
John