CelticFrog
New Member
- Joined
- Apr 19, 2013
- Messages
- 2
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:
What I am trying to get the macro to accomplish (AFTER I do the format as table bit) is this:
Perhaps later I'll add to the macro, but right now it breaks at the first formula entry line.
Here is the ENTIRE code:
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
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:
- add the three columns
- select the first data cell in each column
- 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:
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