EoMonth() Won't work in VBA

3

328mike

New Member
Joined
Jul 5, 2011
Messages
47
Hey all,

I need to use EoMonth in VBA badly and I can't get it to work. I'm using Excel 2007 and I've activated the Analysis tookpack and Analysis for VBA toolpack in Excel. In VBA I've referenced atpvbaen.xls and I get a list of functions but EoMonth is not in that list. What do I have to do to get this to work?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
This should work

Code: 
X = WorksheetFunction.EoMonth(startdate, months)
 
Upvote 0
When I run the code with debug it says unidentified sub or function. I'll try the worksheet function method...but why would EoMonth not appear in the atpvbaen.xls list? I can use it with no problems in Excel.

Code: 
EOM = EoMonth(DataMonth, -1)
With Sheets("Ready for Submission")
    LR2 = .Range("Z" & Rows.Count).End(xlUp).Row
    For m = 2 To LR2
        If Range("F" & m).Value > 0 And Range("F" & m) < EoMonth(DataMonth) - EoMonth(DataMonth, -1) + 1 Then Range("AA" & m).Value = 30
        If EoMonth(DataMonth) + 1 - Range("E" & m).Value < EoMonth(DataMonth) - EoMonth(DataMonth, -1) + 1 Then Range("AA" & m).Value = EoMonth(DataMonth) - Range("E" & m).Value
        Next m
    On Error Resume Next
End With
 
Upvote 0
.................
It needs to be inside a sub or function.

Code: 
Sub Test()
EOM = EoMonth(DataMonth, -1)
With Sheets("Ready for Submission")
    LR2 = .Range("Z" & Rows.Count).End(xlUp).Row
    For m = 2 To LR2
        If Range("F" & m).Value > 0 And Range("F" & m) < EoMonth(DataMonth) - EoMonth(DataMonth, -1) + 1 Then Range("AA" & m).Value = 30
        If EoMonth(DataMonth) + 1 - Range("E" & m).Value < EoMonth(DataMonth) - EoMonth(DataMonth, -1) + 1 Then Range("AA" & m).Value = EoMonth(DataMonth) - Range("E" & m).Value
        Next m
    On Error Resume Next
End With
End Sub

If you think VBA will be useful, learn it.
 
Upvote 0
......

I posted the part of my code that uses EoMonth()

This is my entire code

Code: 
Sub CGMFormattingWIRELINE()
'
' CGMFormatting Macro
' Formats an H20 GC export in the CGM Format.
'
' Keyboard Shortcut: Ctrl+Shift+W
'
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet1").Name = "Closed and Pending Accounts"

'Heading for closed accounts sheet
'Add Counting Object later for full automation
Sheets("Closed and Pending Accounts").Select
    ActiveCell.FormulaR1C1 = "Closed and Pending Accounts"
    Range("A1").Select
    Selection.Font.Bold = True
    Range("A1:Z1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("A2").Select
    
    
' Begin CGM Template Formatting on sheet "Ready for Submission"
    Sheets("Sheet 1").Select
    Sheets("Sheet 1").Name = "Ready for Submission"
    Cells.Select
    Cells.EntireColumn.AutoFit
    Columns("K:K").Select
    Selection.Cut
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    Columns("AA:AA").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Columns("A:A").Select
    Selection.Cut
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    Columns("K:K").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Columns("G:G").Select
    Selection.Cut
    Columns("A:A").Select
    ActiveSheet.Paste
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("C:C").Select
    Selection.Cut
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    Columns("M:M").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Columns("D:D").Select
    Selection.Cut
    Columns("C:C").Select
    ActiveSheet.Paste
     Range("D1").Select
    ActiveCell.FormulaR1C1 = "Order Type"
    Columns("E:I").Select
    Selection.Cut
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    Columns("N:N").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Columns("M:M").Select
    Selection.Cut
    Columns("E:E").Select
    ActiveSheet.Paste
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Original Lifeline Activation Date"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "Cancel Date"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Restart Date"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Tribal"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "ETC"
    Range("J1").Select
    Columns("J:J").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "LinkupEligible"
    Range("J1").Select
    Columns("K:K").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("L:L").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("M:M").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("N:N").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("O:O").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "TLS MRC"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "TLS NRC"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "TLS Customer MRC"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "TLS Customer NRC"
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "LD Plan ID"
    Columns("P:P").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("S:S").Select
    Selection.Cut
    Columns("P:P").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    Columns("R:R").Select
    Selection.Cut
    ActiveWindow.SmallScroll ToRight:=1
    Range("Z1").Select
    ActiveSheet.Paste
    Columns("U:U").Select
    Selection.Cut
    Columns("R:R").Select
    ActiveSheet.Paste
    Columns("V:V").Select
    Selection.Cut
    Columns("S:S").Select
    ActiveSheet.Paste
    Columns("W:W").Select
    Selection.Cut
    Columns("T:T").Select
    ActiveSheet.Paste
    Columns("Y:Y").Select
    Selection.Cut
    Columns("U:U").Select
    ActiveSheet.Paste
    Range("V1").Select
    ActiveCell.FormulaR1C1 = "Plan ID"
    Range("W1").Select
    ActiveCell.FormulaR1C1 = "Plan Description"
    Range("X1").Select
    ActiveCell.FormulaR1C1 = "Self Certification Form Received"
    Columns("Z:Z").Select
    Selection.Cut
    Columns("Y:Y").Select
    ActiveSheet.Paste
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    Columns("R:R").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "Last Name"
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "First Name"
    Range("R2").Select
    
    'Sets the formula range to dynamically match the number of accoutns
    'Fills in Order Type, ETC, and Tribal columns with N, Y, and N respectively
    Dim DataRange As Long 'Counts the number of cells that contain data
    Dim i As Long
    DataRange = Application.WorksheetFunction.CountA(Range("Q:Q")) - 1
    For i = 1 To DataRange
    Cells(1 + i, 18).Value = "=LEFT(RC[-1],FIND("","",RC[-1])-1)"
    Cells(1 + i, 19).Value = "=TRIM(RIGHT(RC[-2],LEN(RC[-2])-LEN(RC[-1])))"
    Cells(1 + i, 8).Value = "N"
    Cells(1 + i, 9).Value = "Y"
    Cells(1 + i, 4).Value = "N"
    Next i
    
    'Deletion of spaces and commas in name fields
    Columns("R:S").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("Q:Q").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("R:R").Select
    Selection.Replace What:=", ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "Address1"
    Range("T1").Select
    ActiveCell.FormulaR1C1 = "Address2"
    Range("T2").Select
    Range("U1").Select
    ActiveCell.FormulaR1C1 = "City"
    Range("U1").Select
    Range("V1").Select
    ActiveCell.FormulaR1C1 = "Zipcode"
    Range("X1").Select
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "BillYyyyMm"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "State"
    Range("B2").Select
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Working Telephone Number"
    Range("C2").Select
    Range("A2").Select
    ActiveWindow.FreezePanes = True

'ETC Billing Month Column (BillYyyyMm)
Dim DataMonth
Dim DataMonthFormatted
Dim ETCMonth
Dim Answer As Integer

DataMonth = Application.InputBox("Please enter a data month. (Mm/01/Yyyy)")
If DataMonth = False Then
Answer = MsgBox("Warning: Macro functionality will be lost without a data month. Do you wish to continue?", vbYesNo, "Cancel Clicked")
    If Answer = 6 Then
        DataMonth = ""
        GoTo Terminate
    ElseIf Answer = 7 Then
        DataMonth = "TriggerErrHandler"
    End If
End If
'Processes date information again if it is entered incorrectly
Dim ErrCount As Long
On Error GoTo ErrHandler
ETCMonth = Month(DataMonth)
    DataMonthFormatted = Format(DataMonth, "Yyyy-Mm")
Dim j As Long
For j = 1 To DataRange
    Cells(1 + j, 1).Formula = DataMonthFormatted
If Month(Cells(1 + j, 5)) = ETCMonth Then
    Cells(1 + j, 10).Value = "Y"
    End If
    Next j
  
'Proration Columns
Range("AA1").Select
    ActiveCell.FormulaR1C1 = "# of Days"
Range("AB1").Select
    ActiveCell.FormulaR1C1 = "Pro Rate $"
Range("AC1").Select
    ActiveCell.FormulaR1C1 = "Deferred $"
    
'Formatting header
Rows("1:1").Select
    Selection.Copy
    Sheets("Closed and Pending Accounts").Select
    ActiveSheet.Paste
    Range("A3").Select
    ActiveWindow.FreezePanes = True
    Sheets("Ready for Submission").Select
    Range("A3").Select

'Begin account filtering code
Dim k As Long, LR As Long
With Sheets("Ready for Submission")
    LR = .Range("Z" & Rows.Count).End(xlUp).Row
    For k = 2 To LR
        If Range("Z" & k).Value = "CLOSED" And .Range("AJ" & k).Value < CDate(DataMonth) Then .Rows(k).Cut Destination:=Sheets("Closed and Pending Accounts").Range("A" & Rows.Count).End(xlUp).Offset(1)
        If Range("Z" & k).Value = "PENDING" Then .Rows(k).Cut Destination:=Sheets("Closed and Pending Accounts").Range("A" & Rows.Count).End(xlUp).Offset(1)
        If Range("Z" & k).Value = "CLOSED" And Range("AJ" & k).Value - CDate(DataMonth) < 30 And Range("AJ" & k).Value - CDate(DataMonth) > 0 Then Range("AJ" & k).Cut Destination:=Sheets("Ready for Submission").Range("F" & k)
       Next k
    On Error Resume Next
    .Range("Z1:Z" & LR).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    On Error GoTo 0
End With


Dim m As Long, LR2 As Long, EOM As Date
EOM = WorksheetFunction.EoMonth(DataMonth, -1)
With Sheets("Ready for Submission")
    LR2 = .Range("Z" & Rows.Count).End(xlUp).Row
    For m = 2 To LR2
        If Range("F" & m).Value > 0 And Range("F" & m) < EoMonth(DataMonth) - EoMonth(DataMonth, -1) + 1 Then Range("AA" & m).Value = 30
        If EoMonth(DataMonth) + 1 - Range("E" & m).Value < EoMonth(DataMonth) - EoMonth(DataMonth, -1) + 1 Then Range("AA" & m).Value = EoMonth(DataMonth) - Range("E" & m).Value
        Next m
    On Error Resume Next
End With

Dim ClosedCount As Long
Sheets("Closed and Pending Accounts").Select
With Sheets("Closed and Pending Accounts")
ClosedCount = .Range("Z" & Rows.Count).End(xlUp).Row
    Range("A3" & ":Z" & ClosedCount).Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent6
            .TintAndShade = 0.599993896298105
            .PatternTintAndShade = 0
        End With
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
            Selection.Borders(xlDiagonalUp).LineStyle = xlNone
                With Selection.Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlMedium
                End With
                With Selection.Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlMedium
                End With
                With Selection.Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlMedium
                End With
                With Selection.Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlMedium
                End With
            Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("A2" & ":Z2").Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
            Selection.Borders(xlDiagonalUp).LineStyle = xlNone
                With Selection.Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlMedium
                End With
                With Selection.Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlMedium
                End With
                With Selection.Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlMedium
                End With
                With Selection.Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Weight = xlMedium
                End With
            Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
'Counting object that shows number of closed accounts
Range("A1").Value = ClosedCount - 2 & " Closed and Pending Accounts"
Cells.Select
    Cells.EntireColumn.AutoFit
Range("A2").Select

'Sorting closed and pending accounts
ActiveWorkbook.Worksheets("Closed and Pending Accounts").Sort.SortFields.Add _
        Key:=Range("Z3" & ":Z" & ClosedCount + 2), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Closed and Pending Accounts").Sort.SortFields.Add _
        Key:=Range("E3" & ":E" & ClosedCount + 2), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Closed and Pending Accounts").Sort
        .SetRange Range("A3" & ":AK" & ClosedCount + 2)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

'Counting Object that shows the number of accounts missing information
'Sheets("Missing Information").Select
'Range("A1").Value = MICount - 2 & " Accounts Missing Information"
'Cells.Select
'    Cells.EntireColumn.AutoFit

'Sheets("Ready for Submission").Select
'Columns("Z:Z").Select
'    Selection.Delete Shift:=xlToLeft
'Range("A2").Select


'Exit point for empty data month entries
Terminate:
Exit Sub
'Begin Error Handling
Exit Sub
ErrHandler:
ErrCount = ErrCount + 1
DataMonth = Application.InputBox _
        ("Please enter the closest date that resembles " & _
        DataMonth & _
        ", in format (Mm/01/Yyyy)", "Invalid Date Format!")

'Easter Egg
If ErrCount = 50 Then
MsgBox "I've just picked up a fault in the AE35 unit. It's going to go 100% failure in 72 hours Dave. ", vbCritical, "HAL"
DataMonth = Application.InputBox _
        ("Please enter the closest date that resembles " & _
        DataMonth & _
        ", in format (Mm/01/Yyyy)", "Invalid Date Format!")
        If DataMonth = False Then
            Answer = MsgBox("Warning: Macro functionality will be lost without a data month. Do you wish to continue?", vbYesNo, "Cancel Clicked")
                If Answer = 6 Then
                    DataMonth = ""
                    GoTo Terminate
                Else
                DataMonth = "ThisWillTriggerErrHandler"
                End If
        End If
ElseIf ErrCount = 100 Then
MsgBox "Just what do you think you're doing, Dave? ", vbCritical, "HAL"
DataMonth = Application.InputBox _
        ("Please enter the closest date that resembles " & _
        DataMonth & _
        ", in format (Mm/01/Yyyy)", "Invalid Date Format!")
        If DataMonth = False Then
            Answer = MsgBox("Warning: Macro functionality will be lost without a data month. Do you wish to continue?", vbYesNo, "Cancel Clicked")
                If Answer = 6 Then
                    DataMonth = ""
                    GoTo Terminate
                Else
                DataMonth = "ThisWillTriggerErrHandler"
                End If
        End If
ElseIf ErrCount = 150 Then
MsgBox "Cake, and grief counseling, will be available at the conclusion of the test. ", vbExclamation, "GLaDOS"
DataMonth = Application.InputBox _
        ("Please enter the closest date that resembles " & _
        DataMonth & _
        ", in format (Mm/01/Yyyy)", "Invalid Date Format!")
        If DataMonth = False Then
            Answer = MsgBox("Warning: Macro functionality will be lost without a data month. Do you wish to continue?", vbYesNo, "Cancel Clicked")
                If Answer = 6 Then
                    DataMonth = ""
                    GoTo Terminate
                 Else
                 DataMonth = "ThisWillTriggerErrHandler"
                End If
        End If
ElseIf DataMonth = False Then
Answer = MsgBox("Warning: Macro functionality will be lost without a data month. Do you wish to continue?", vbYesNo, "Cancel Clicked")
    If Answer = 6 Then
        DataMonth = ""
        GoTo Terminate
        Else
        DataMonth = "ThisWillTriggerErrHandler"
    End If
End If
Resume
End Sub

I thought that was pretty straightforward to assume that...
 
Upvote 0
EVERY instance of EoMonth needs to be preceeded by WorksheetFunction (or Application)
WorksheetFunction.EoMonth(...)
or
Application.EoMonth(...)
 
Upvote 0
Add WorksheetFunction.EoMonth to every Eomonth you see.
and please just post the code that's related O_o

and you can clean up your code by removing things like Select.
Code: 
Worksheets("Blarg").Select
Selection.Name = "Ha"
could be reduced to
Code: 
Worksheets("Blarg").Name = "Ha"
 
Upvote 0
When I run the code I get a compile error that says:

Compile Error:

Argument not optional.

Pointing to this line of code:

Code: 
If Range("F" & m).Value > 0 And Range("F" & m) < WorksheetFunction.EoMonth(DataMonth) - WorksheetFunction.EoMonth(DataMonth, -1) + 1 Then Range("AA" & m).Value = WorksheetFunction.EoMonth(DataMonth) - WorksheetFunction.EoMonth(DataMonth, -1) + 1

Is this because I am trying to use EoMonth on a VBA declared variable and not something in the excel file?
 
Last edited:
Upvote 0
328mike said:
Is this because I am trying to use EoMonth on a VBA declared variable and not something in the excel file?
Click to expand...

No, just remember what EoMonth's parameters are.
I'm sure it's because of that or something is out of range.

Haven't looked at your code because it's hard to scroll through.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Excel Pivot-Table calculated fields with if statement return wrong results
Replies
3
Views
405
RoryA
RoryA
J
  • Solved
Determine total numbers of days worked in a given month/year from a table of date ranges
Replies
3
Views
565
Eric W
Eric W
M
  • Solved
How to move custom VBA/ribbons
2
Replies
12
Views
598
missingsc
M
V
  • Question Question
Help with my excel sheet
Replies
2
Views
293
victoriar
V
A
  • Question Question
Purchase Order macros
Replies
5
Views
345
AndyB86
A

Forum statistics

Threads
1,224,551
Messages
6,179,472
Members
452,915
Latest member
hannnahheileen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top