Excel VBA Type mismatch using range data type-CodePudding

I am having a hard time understanding why i am getting a 'Type mismatch' error on my code.

the code takes 2 workbooks and copies specific data based off there value and then pastes it into the destination workbook. It has been working fine previously, but ever since yesterday after i tweaked it a little it no longer works.

Dim StatusCol As Range
Dim StatusCol2 As Range
Dim Status As Range
Dim PasteCell As Range
Dim SCH22 As Workbook
Dim SCH21 As Workbook
Dim BD As Workbook

Set SCH22 = Workbooks.Open("path to first workbook")
Set SCH21 = Workbooks.Open("path to second workbook")
Set BD = Workbooks.Open("path to pasting workbook")

Set StatusCol = SCH22.Sheets("CONTFRM22-23").Range("T2:T5000")
Set StatusCol2 = SCH21.Sheets("CONTFRM20-21").Range("R2:R5000")

ThisWorkbook.Sheets("2022-23").Range("A2:AC5000").ClearContents
ThisWorkbook.Sheets("2020-21").Range("A2:R5000").ClearContents


For Each Status In StatusCol
    If BD.Sheets("2022-23").Range("A2") = "" Then
        Set PasteCell = BD.Sheets("2022-23").Range("A2")
    Else
        Set PasteCell = BD.Sheets("2022-23").Range("A1").End(xlDown).Offset(1, 0)
    End If
    
    If Status.Value > 31 Then Status.Offset(0, -19).Resize(1, 31).Copy PasteCell
    
Next Status

For Each Status In StatusCol2
    If BD.Sheets("2020-21").Range("A2") = "" Then
        Set PasteCell = BD.Sheets("2020-21").Range("A2")
    Else
        Set PasteCell = BD.Sheets("2020-21").Range("A1").End(xlDown).Offset(1, 0)
    End If
    
    If Status.Value > 31 Then Status.Offset(0, -17).Resize(1, 29).Copy PasteCell
    
Next Status

end sub

i get the error on the line

If Status.Value > 31 Then Status.Offset(0, -17).Resize(1, 29).Copy PasteCell

I am unsure why as i do not get the error when it runs it for the first spreadsheet, only the second spread sheet

any suggestions? Thanks

CodePudding user response：

The last line of the immediate window says 'Error 2015'

This means that Status contains a #VALUE! error, which cannot be compared to a number like 31.

Use IsNumeric to check before comparing to 31.

If IsNumeric(Status.Value) Then
   If Status.Value > 31 Then 
       Status.Offset(0, -17).Resize(1, 29).Copy PasteCell
   End If
End If