RunTime Error 13 -- TYPE MISMATCH

[sgm1945]

Starting from row 7 onwards (row8, row9, row10...), I've got my data in place. When I run the macro below, it works fine.

However, if there is only one row of data in row7, when I run the macro it comes up a RunTime Error 13 type mismatch at the line of code --

TheExch = ws.Cells(InputRow, 66)

.

The cell value in BN7 (row7, column 66) is =BM7*AK7 and the result is 540559.033224.

If there is more than one row of data, my macro works fine. How would I solve this?

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 Gross As Double
    Dim Com As Double
    Dim Exch As Double
    Dim OldMonthsRow As Long
    Dim InputRow As Long
    Dim OutputRow As Long
    Dim TheDate As Date
    Dim TheAmount As Long
    Dim TheGross As Double
    Dim TheCom As Double
    Dim TheExch As Double
    Dim ws As Worksheet


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


 ActiveWindow.SmallScroll Down:=21
    Rows("40:59").Select
    Selection.ClearContents
    
    ' 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) - 0, 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
    Gross = 0
    Com = 0
    Exch = 0




    ' PROCESS THE DATA
    OutputRow = OldMonthsRow
    For InputRow = 7 To LastDataRow
    TheDate = ws.Cells(InputRow, 6)
    TheAmount = ws.Cells(InputRow, 8)
    TheGross = ws.Cells(InputRow, 61)
    TheCom = ws.Cells(InputRow, 37)
    TheExch = ws.Cells(InputRow, 66)
        
        If TheDate >= CutoffDate Then
            ' Add at the bottom of the summary table
            OutputRow = OutputRow + 1
            
            Rows(InputRow).Select
    Selection.Copy
    ActiveWindow.SmallScroll Down:=15
    Rows(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
            Gross = Gross + TheGross
            Com = Com + TheCom
            Exch = Exch + TheExch
        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
    ws.Cells(OldMonthsRow, 61).Formula = Gross
    ws.Cells(OldMonthsRow, 37).Formula = Com
    ws.Cells(OldMonthsRow, 66).Formula = Exch


    Set ws = Nothing
    
      Set ws = ActiveSheet
      If ws.Cells(45, 8) = 0 Then Rows([45]).EntireRow.Delete
    Set ws = Nothing
    
    Sheets("Raw").Select
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=COUNTA(R[44]C[4]:R[64]C[4])"
    
    
End Sub

 

[Michael M]

Works fine for me if LastDataRow is equal or greater than 7, even if there is only one row
Can Lastdatarow be less than 7....this will give the error
Try using this to see if that solves the issue, but a more robust solution needs to be applied

For InputRow = 7 To LastDataRow
    On Error Resume Next
   TheDate = ws.Cells(InputRow, 6)
    TheAmount = ws.Cells(InputRow, 8)
    TheGross = ws.Cells(InputRow, 61)
    TheCom = ws.Cells(InputRow, 37)
    TheExch = ws.Cells(InputRow, 66)

 

[sgm1945]

Thanks Michael - I typed in "On Error Resume Next" as you suggested and it works. However, my macro keeps copying row 7 into column 46, 47,48,49,50 -- it goes on and on, so I have to end the excel process to stop it from running.

How would I modify my code so it will just copy row 7 once? Because my intention with this code is just to copy row 7 into column 45.

 

[sgm1945]

Typo --

row 7 into row 46, 47,48,49,50

Because my intention with this code is just to copy row 7 into column 46.

Currently my macro copies row 7 into row 46, 47,48,49,50 and on and on.

I just want to copy row 7 into row 45.

 

[sgm1945]

Update:

If I change my original code (from the 1st post):

For InputRow = 7 To LastDataRow

to

For InputRow = 7 To 7

My macro works perfectly.

Is there something wrong with my "LastDataRow" code?

LastDataRow = ws.Range("H" & CStr(7)).End(xlDown).Row

 

[Michael M]

a simpler option for last row is

lastdatarow=cells(rows.count,"H").end(xlup).row

as this goes to the bottom of the sheet, then comes back up until it finds something in Col "H"
Yours can get tripped up if there is a blank before the end of the data in Col "H"

 

[sgm1945]

Michael, thanks. Its working now!