Excel 2010 VBA - Error 1004 trying to assign a function to a cell

[CelticFrog]

I "recorded" a macro several days ago, then went in using VBA and cleaned the HECK out of it because as expected, it didn't run.

My problem? It's still not running. Grr.

Background/Process:

1. Every month (or more often) I run several queries out of Access and then export the data (columns identical) to between 5-7 excel spreadsheets.
2. I then open each spreadsheet and do the following:

• manually select all the actual data
• format as a table
• add three columns
• set the formula for each column
  • this formula is exactly the same, EXCEPT for dates every month
• other stuff that has nothing to do with this macro so we'll ignore for now

What I am trying to get the macro to accomplish (AFTER I do the format as table bit) is this:

1. add the three columns
2. select the first data cell in each column
3. add the appropriate formula in each column

Perhaps later I'll add to the macro, but right now it breaks at the first formula entry line.

Here is the ENTIRE code:

Option Explicit
    Public StartDate As Date
    Public StartFormula As String
    Public EndDate As Date
    Public EndFormula As String
    
Sub AddColumns()


' Start MessageBox for dates


    ShowDateBox
    CreateFormulas
    
' Start Actual Formula to Create/Edit Columns

    Range("K1").Select
    ActiveCell.Formula = "EffectiveDate"
    Range("L1").Select
    ActiveCell.Formula = "PCPThruDate"
    Range("M1").Select
    ActiveCell.Formula = "BilledSvcInQtr"
    Range("K2").Select
    ActiveCell.Formula = "=IF([@MinOfDATEFROM]<[@PCPFROMDT],(IF([@PCPFROMDT]<" & StartFormula & "," & StartFormula & ",[@PCPFROMDT])),(IF([@MinOfDATEFROM]<" & StartFormula & "," & StartFormula & ",[@MinOfDATEFROM])))"
    Range("L2").Select
    ActiveCell.Formula = "=IF(ISBLANK([@MaxOfPCPTHRUDT])," & EndFormula & ",IF([@MaxOfPCPTHRUDT]>" & EndFormula & "," & EndFormula & ",[@MaxOfPCPTHRUDT]))"
    Range("M2").Select
    ActiveCell.Formula = "=IF([@MaxOfDATEFROM]>=" & StartFormula & ",IF([@MaxOfDATEFROM]<=" & EndFormula & ",""Y"",""""),"""")"
    Range("K2").Select
    Columns("K:K").Select
    Selection.NumberFormat = "m/d/yyyy"
End Sub


Sub ShowDateBox()
    StartDate = InputBox("Enter Quarter Start Date", "Start Date", Format(Now(), "m/d/yyyy"))
    Dim ErrorMsg As String
    
    If IsNull(StartDate) = True Then
        ErrorMsg = msgbox("Please Enter A Start Date", vbOKOnly, "Error")
        StartDate = InputBox("Enter Quarter Start Date", "Start Date", Format(Now(), "m/d/yyyy"))
    End If


    If IsDate(StartDate) = False Then
        Run "ShowDateBox"
    End If


    EndDate = InputBox("Enter Quarter End Date", "End Date", Format(Now(), "m/d/yyyy"))
        If IsNull(EndDate) = True Then
    ErrorMsg = msgbox("Please Enter An End Date", vbOKOnly, "Error")
        EndDate = InputBox("Enter Quarter End Date", "End Date", Format(Now(), "m/d/yyyy"))
    End If


    If IsDate(EndDate) = False Then
        Run "ShowDateBox"
    End If
End Sub


Sub CreateFormulas()
    If IsNull(StartDate) = True Then ShowDateBox Else
    StartFormula = "DATE(YEAR(" & StartDate & "),(MONTH(" & StartDate & "),DAY(" & StartDate & "))"


    If IsNull(EndDate) = True Then ShowDateBox Else
    EndFormula = "DATE(YEAR(" & EndDate & "),(MONTH(" & EndDate & "),DAY(" & EndDate & "))"
End Sub

Currently, when I try to run the macro, I get Error 1004 when it hits the L2 ActiveCell.Formula line. I have gone crazy trying to track it down and this is where I've stopped, thrown my hands in the air, and screamed into the pillow. And I don't even have a pillow here at work.

Can anyone help? I'm hoping it's just a fresh pair of eyes situation and the problem actually has a simple solution.

Thanks!

CF

 

[RoryA]

You would need to remove the opening parentheses before the MONTH function here:

Sub CreateFormulas()
    If IsNull(StartDate) = True Then ShowDateBox Else
    StartFormula = "DATE(YEAR(" & StartDate & "),(MONTH(" & StartDate & "),DAY(" & StartDate & "))"


    If IsNull(EndDate) = True Then ShowDateBox Else
    EndFormula = "DATE(YEAR(" & EndDate & "),(MONTH(" & EndDate & "),DAY(" & EndDate & "))"
End Sub

but the formula still won't work if you pass a date like that. Try using:

Sub CreateFormulas()
    If IsNull(startdate) = True Then ShowDateBox Else
    StartFormula = "DATEVALUE(""" & startdate & """)"


    If IsNull(EndDate) = True Then ShowDateBox Else
    EndFormula = "DATEVALUE(""" & EndDate & """)"
End Sub

 

[CelticFrog]

You. Rock. THANK YOU SO MUCH!!!

 

[RoryA]

You're welcome. Have a good weekend!