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]

Did you mean EOMONTH ?

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

Also, are you getting the correct date from column F? You find the FinalRow from column A. Then from that FinalRow you do a 2nd xlUp in column F. Is that what you really want? The Date from column F is not from the same row as FinalRow.

Is this what you want for the transaction date
glDate = Cells(FinalRow, 6).Value

 

[Oaktree]

In VBA, go to tools --> references and check atpvbaen.xls (the reference for the analysis toolpak in VBA).

Then you can use EOMONTH(glDate,0) directly in your VBA instead of the EVALUATE command that's giving you issues.

 

[Xceller]

Yes, EOMONTH is what I intended to use.

The FinalRow from column A is to measure the pasting range.

glDate = Cells(FinalRow, 6).value ' date value is 5/27/2011

I want to take this date 5/27/2011 and turn it into 5/31/2011
by using the EOMONTH function.

 

[Xceller]

Tried checking atpvbaen.xls

The return date is still 1/31/1900
I think the problem is in the last line of code

This is code:
Sub GLsort()

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

Cells(2, 14).Resize(Rows.Count - 1, 3).Clear
Cells(2, 15).Resize(FinalRow - 2, 1).Formula = "=TRIM(B2&C2)"
Cells(2, 14).Resize(FinalRow - 2, 1).Formula = "=IF(ISNUMBER(IF(AND(LEFT(O1,5)<>""Total"",LEFT(O2,5)=""Total""),VALUE(TRIM(MID(O2,7,4))),""""))=TRUE,IF(AND(LEFT(O1,5)<>""Total"",LEFT(O2,5)=""Total""),VALUE(TRIM(MID(O2,7,4))),""""),"""")"
Cells(2, 16).Resize(FinalRow - 2, 1).Formula = "=IF(N2="""","""",L2)"
Cells(FinalRow, 16).FormulaR1C1 = "=SUM(R[-" & FinalRow - 2 & "]C:R[-1]C)"
Cells(2, 16).Resize(FinalRow, 1).NumberFormat = "#,##0.00"
Cells(2, 14).Resize(FinalRow - 2, 3).Copy
Cells(2, 14).Resize(FinalRow - 2, 3).PasteSpecial Paste:=xlPasteValues

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


End Sub

 

[Oaktree]

Do you want the EOMONTH function in your output? Or just the date the EOMONTH function returns?

If you want the function, do you want it to link to the glDate cell? Or, do you want the hardcoded value in the function?

 

[AlphaFrog]

What date does the MsgBox return?

Sub GLsort()

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

Cells(2, 14).Resize(Rows.Count - 1, 3).Clear
Cells(2, 15).Resize(FinalRow - 2, 1).Formula = "=TRIM(B2&C2)"
Cells(2, 14).Resize(FinalRow - 2, 1).Formula = "=IF(ISNUMBER(IF(AND(LEFT(O1,5)<>""Total"",LEFT(O2,5)=""Total""),VALUE(TRIM(MID(O2,7,4))),""""))=TRUE,IF(AND(LEFT(O1,5)<>""Total"",LEFT(O2,5)=""Total""),VALUE(TRIM(MID(O2,7,4))),""""),"""")"
Cells(2, 16).Resize(FinalRow - 2, 1).Formula = "=IF(N2="""","""",L2)"
Cells(FinalRow, 16).FormulaR1C1 = "=SUM(R[-" & FinalRow - 2 & "]C:R[-1]C)"
Cells(2, 16).Resize(FinalRow, 1).NumberFormat = "#,##0.00"
Cells(2, 14).Resize(FinalRow - 2, 3).Copy
Cells(2, 14).Resize(FinalRow - 2, 3).PasteSpecial Paste:=xlPasteValues

glDate = Cells(FinalRow, 6).End(xlUp).Value
[COLOR="Red"]MsgBox Format(glDate, "mmm d, yyyy")[/COLOR]
Cells(2, 13).Resize(FinalRow - 2, 1).Formula = "=EOMONTH(" & glDate & ",0)"


End Sub

 

[Xceller]

MsgBox returns May 27, 2011

It's really strange. In Excel formula bar, it reads =EOMONTH(5/27/2011,0) but the value is 1/31/1900. However if I manually change it to =EOMONTH("5/27/2011",0) then it works (5/31/2011).

 

[AlphaFrog]

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

Or just this...
Cells(2, 13).Resize(FinalRow - 2, 1).Formula = EOMONTH(DateSerial(glDate), 0 )


Or just dim glDate as a Date type
Dim glDate As Date

 

[Xceller]

I get an error message:

Compile error:
Argument not optional