How to use EMONTH function in vba?

[Xceller]

Hi everyone out there. I have a report output from an accounting system that I need to clean up and date it. Any help is appreciated.

Column 6 contains transaction dates but not neccessary the last day of the month. I am hoping to use the EMONTH function to turn this date to the last day of the month and output to a range. glDate = 5/27/2011 but what I get is 1/31/1900.

Here are the lines of codes that I have trouble with:

Sub glSort()

FinalRow = Cells(Rows.count, 1).End(XlUp).Row

glDate = Cells(FinalRow, 6).End(XlUp).Value

Cells(2, 13).Resize(FinalRow-2, 1).Formula = Evaluate ("=EMONTH(" & glDate & ",0)")

End Sub

 

[AlphaFrog]

Sorry. DateSerial should be DateValue

 

[Rick Rothstein]

It's really strange. In Excel formula bar, it reads =EOMONTH(5/27/2011,0) but the value is 1/31/1900.

Excel sees the value between EOMONTH's parentheses as 5 divided by 27 divided by 2011 which is effectively 0 to the function, so your are getting the end date for the first date the Excel can handle. You can use the DateValue approach that AlphaFrog suggested or you can use this alternate method to get Excel to recognize glDate's content as a real date...

Cells(2, 13).Resize(FinalRow - 2, 1).Formula = "=EOMONTH(--(""" & glDate & """),0)"

EDIT
Actually, we do not have to force the glDate to be real date... just a quoted text string is enough...

Cells(2, 13).Resize(FinalRow - 2, 1).Formula = "=EOMONTH(""" & glDate & """,0)"

 

[AlphaFrog]

...or just dim glDate as a date type

[COLOR="Red"]Dim glDate as Date[/COLOR]
glDate = Cells(FinalRow, 6).End(xlUp).Value
Cells(2, 13).Resize(FinalRow - 2, 1).Formula = "=EOMONTH(" & glDate & ",0)"

 

[Xceller]

Thanks a million. This works! Thanks everybody.

I think AlphaFrog's approach is more intuitive for me by using DateValue
but I couldn't get it work. Any idea?

Cells(2, 13).Resize(FinalRow - 2, 1).Formula = "=EOMONTH(" & DateValue(glDate) & ",0)"

return value still 0 (1/31/1900)

 

[AlphaFrog]

It works but it doesn't work??? What worked?

 

[Xceller]

Cells(2, 13).Resize(FinalRow - 2, 1).Formula = "=EOMONTH(--(""" & glDate & """),0)" '======= This works

Cells(2, 13).Resize(FinalRow - 2, 1).Formula = "=EOMONTH(""" & glDate & """,0)" ' ========This works

Dim glDate As Date
glDate = Cells(FinalRow, 6).End(xlUp).Value
Cells(2, 13).Resize(FinalRow - 2, 1).Formula = "=EOMONTH(" & glDate & ",0)" '=====Doesn't work

Thanks a lot for all your help. I like your DateValue approach. It's more intuitive for me but I just can't make it work.

 

[Xceller]

Rick Rothstein provided 2 solutions that work pretty good.

To your earlier question, I just wanted the hardcoded value in the output cells.

Thanks for your help.

 

[AlphaFrog]

I don't know why DateValue doesn't coerce the text-date into a serial date. As long as you have a working solution.

If you just want the hardcoded value in the output cells...
Cells(2, 13).Resize(FinalRow - 2, 1).Value = EOMONTH(glDate, 0)

This assumes you still have referenced atpvbaen.xls

 

[Rick Rothstein]

If you just want the hard-coded date in that cell, you do not need to call out to the EOMONTH function at all, you can do it with native VB functions...

Cells(2, 13).Resize(FinalRow - 2, 1).Value = DateSerial(Year(glDate), Month(glDate) + 1, 0)

 

[Xceller]

Thank you for all your help. It works great.