VBA function gives error after deleting rows or columns

[auche2002]

Hi, I work a lot with sheets that have historical daily data. I've created a function that allows me to pass the header of my data column, an end date and a number of months. The function then adds all the values in the data column corresponding to the end of the month of my end date and going backwards the number of months I specified. EX: I have dates for all of 2011 in column B and tons produced for each day in column C. I pass B5 (the header cell of the data), December 15, 2011, and 6. The function adds tons produced for all of December and the previous 5 months (total 6 months). I realize there's probably a way to do this with pivot tables or normal formulas but I wanted a function so I can call it from a sub later on.

The function works fine initially. The problem arrises if I delete a row or column above or to the left of the data range. Then the funciton thinks for 5-10 seconds and executes the first msgbox which should only occur if the end date originally passed to the funciton is not a valid date. The date was valid when I originally entered the function and after clicking OK on the msgbox, the function still returns the correct value, oddly enough. It seems like if the date was now invalid, then the function would execute the next line of code, exit and return 0. Right now the problem is just annoying but it may cause more errors if I call the function from a sub later on.

Below is my code. This is my first time using code tags so I appologize if it doesn't paste correctly. Don't laugh, I'm sure there's a quicker and easier way to do most of this but I'm a novice.

Function PreviousMonthSum(DataHeader As Range, EndDate As Variant, NumMonths As Long) As Single
    '   Calculates the sum of a given data set for the specified number of months previous to _
        a specified date
    Dim RowNum As Long, StartRow As Long, StartColumn As Long
    Dim LastRow As Long, DateCell As String, c As Variant, NumYears As Single
    Dim StartMonth As Long, StartYear As Long, StartDay As Long, DataColumn As Long
    Dim EndMonth As Long, EndYear As Long, EndDay As Long, SheetName As String
    Dim PlaceHolder As String, LastCell As String, Rng As Range
    
    PreviousMonthSum = 0
    SheetName = DataHeader.Parent.Name  'Find the sheet that holds the data so the function can _
        be used from any sheet in the workbook
    PlaceHolder = DataHeader.Address
    DataColumn = Range(PlaceHolder).Column  'Find the column number of the data the user wants _
        to sum
    
    If Not IsDate(EndDate) Then
        MsgBox "Please enter a valid date as the start month", vbOKOnly
        Exit Function
    End If  'Verify that the end date is a valid date and prompt user if not
    
    EndMonth = Month(EndDate)
    EndYear = Year(EndDate)
    'Find the month and year of the last month in the sum
    
    NumYears = Int(NumMonths / 12)
    
    If NumMonths Mod 12 > Month(EndDate) Then
        NumYears = NumYears + 1
    End If
    
    StartYear = Year(EndDate) - NumYears
    
    StartMonth = Month(EndDate) - NumMonths Mod 12 + 13
    
    If StartMonth > 12 Then
        StartMonth = StartMonth - 12
    End If
    'Find the month and year of the first month in the sum
    
    StartDay = DateSerial(StartYear, StartMonth, 1)
    EndDay = DateSerial(Year(EndDate), Month(EndDate) + 1, 0)
    'Find the first and last days in the date range to include in the sum
    
    LastCell = Sheets(SheetName).Cells.Find(What:="*", After:=Sheets(SheetName).[A1], _
        SearchDirection:=xlPrevious).Address    'Find the last cell in the data range to be _
            used to specify the range in the following find block
    
    With Sheets(SheetName).Range("A1", LastCell)
        On Error GoTo ErrorHandler
        Set Rng = .Find(What:="Date", After:=.Cells(.Cells.Count), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)
    End With    'Finds the cell with the "Date" heading
    
    DateCell = Rng.Address
    
    StartColumn = Range(DateCell).Column
    StartRow = Range(DateCell).Row + 1
    
    Do While Not IsDate(Sheets(SheetName).Cells(StartRow, StartColumn))
        StartRow = StartRow + 1
    Loop    'Finds the start row of the data if there are cells between the heading and the _
             first date
    
    LastRow = Range(LastCell).Row  'Finds the last row of data
                
    With Sheets(SheetName)
        For Each c In .Range(.Cells(StartRow, StartColumn), .Cells(LastRow, _
        StartColumn)).Cells
            If (c.Value) >= StartDay And (c.Value) <= EndDay Then
                'Determine if the date is within the requested timeframe
                RowNum = c.Row
                PreviousMonthSum = PreviousMonthSum + .Cells(RowNum, DataColumn).Value
                'Adds the data in the requested column to find the Previous Months Sum
            End If
        Next
    End With
Exit Function
    
ErrorHandler:
    MsgBox "You must have dates in a column with Date - not case sensitive - as the heading."
    'Informs the user that the data set must have a date column with "Date" as the heading
    
End Function

Is there some command I can add that will make the function realize the date hasn't changed?

Thanks for any help. Sorry for the long narrative, just trying to explain what's happening somewhat understandably.

 

[auche2002]

Sorry, I forgot to include that I'm running Windows XP and XL2010.

 

[auche2002]

I've found 2 more minor issues this morning that I can't find the answers to.

If I pass the function a cell that contains "12/15/08" as the EndDate it works just fine. However, if I type "12/15/08" into the formula bar or the insert function box for EndDate, it passes 12 divided by 15 divided by 8 (0.1) to the EndDate variable. It does the same thing if I have EndDate as a variant, date or string variable, although if I have it as a date it passes something like 12:00:34AM. I have it as a variant now so that I can msgbox the user if they enter something other than a valid date. Is there anthing I can do to make it work with a cell reference for the date or the date just typed in?

The other issue is that if I click the insert function button to the left of the formula bar and enter the function arguments, it works just fine if I click on cells for each of the 3 arguments. However, if I type 3 (or some other number) in the box for number of months, the function executes my ErrorHandler code immediately after I hit the 3 key and displays the msgbox. Then I click OK on the msgbox and Ok on the insert function box and the function executes correctly. It doesn't do this if I type in a date for the EndDate into the second box, but then I have the problem described above. Is there a way to keep anything from executing until I click on Ok on the insert function box?

Minor problems I know, but if anyone can give me an answer without spending a lot of time I'd greatly appreciate it.