If date is greater than [MACRO] not working

[sgm1945]

In column F, I have dates. I would like to program a macro that would do the following:

For the data starting from Row 7, if the date in Column F is greater than the previous month (means greater than 1st July 2015), copy that entire row and paste special values starting from row 46.
If the date in Column F is before 1st July 2015, output the data as the earliest and last date (11/05/2015 - 16/05/2015) and the sums from column H (60), and paste it on Row 45.

When the run the code, there is a "Type Mismatch Error" on the coding line Rows("InputRow:InputRow").Select .

Any help will be appreciated, thanks.

Sub Summary()


    Dim LastDataRow As Long
    Dim DataRow As Long
    Dim cDates As Long
    Dim cAmounts As Long
    Dim cI As Double
    Dim cAK As Double
    Dim cBM As Double
    Dim CutoffDate As Date
    Dim EarliestOldDate As Date
    Dim LatestOldDate As Date
    Dim SumOfOld As Long
    Dim OldMonthsRow As Long
    Dim InputRow As Long
    Dim OutputRow As Long
    Dim TheDate As Date
    Dim TheAmount As Long
    Dim ws As Worksheet


    ' INITIALIZE
    ' Assume we're operating on the activesheet
    Set ws = ActiveSheet


    ' Assume data is a contiguous block
    LastDataRow = ws.Range("H" & CStr(7)).End(xlDown).Row


    ' Calculate row where sum of old months goes
    OldMonthsRow = 45


    ' Calculate the cutoff date = first date of current month
    CutoffDate = DateSerial(Year(Date), Month(Date) - 1, 1)


    ' Column where dates are
    cDates = 6
    
    ' Column where amounts are
    cAmounts = 8


    ' Initialize earliest and latest old dates, and sum of old
    EarliestOldDate = DateSerial(3000, 12, 31)  ' Way out in the future
    LatestOldDate = DateSerial(1904, 1, 1)      ' Way back in the past
    SumOfOld = 0




    ' PROCESS THE DATA
    OutputRow = OldMonthsRow
    For InputRow = 7 To LastDataRow
    TheDate = ws.Cells(InputRow, 6)
    TheAmount = ws.Cells(InputRow, 8)
        
        If TheDate >= CutoffDate Then
            ' Add at the bottom of the summary table
            OutputRow = OutputRow + 1
            
            Rows("InputRow:InputRow").Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=15
    Rows("OutputRow:OutputRow").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
         
        Else
            ' Update results for previous months
            EarliestOldDate = IIf(TheDate < EarliestOldDate, TheDate, EarliestOldDate)
            LatestOldDate = IIf(TheDate > LatestOldDate, TheDate, LatestOldDate)
            SumOfOld = SumOfOld + TheAmount
        End If


    Next InputRow


    ' WRITE RESULTS TO SUMMARY ROW
    ws.Cells(OldMonthsRow, cDates).Formula = Format(EarliestOldDate, "dd/mm/yyyy") & " - " & Format(LatestOldDate, "dd/mm/yyyy")
    ws.Cells(OldMonthsRow, cAmounts).Formula = SumOfOld


    Set ws = Nothing
End Sub

 

[Teeroy]

Rather than evaluating the variable you've created a literal String with the following line:

Rows("InputRow:InputRow").Select

Replace with:

Rows(InputRow & ":" & InputRow).Select

BTW I haven't done any optimisation but you almost never need to use Select. It will slow down your code.

 

[Domski]

I think you just need:

[B]Rows(InputRow).Select[/B]

Dom

 

[sgm1945]

Thanks guys -- I've needed to replace two lines of codes, actually.

Rows(InputRow).Select
Rows(OutputRow).Select

It works perfectly, however, if the dates in column F are all greater than the previous month, the output on Row 45 is as below. How would I modify the macro, to paste from Row 45 onwards instead of starting at row 46, and not to include the 31/12/3000-01/01/2014?