Run-time error 13

[briandpearson]

I'm very new to VBA and I just wrote a macro (mostly modifying code I copied from these message boards) that seems to be working correctly, but I keep getting the Error 13 message. When I go to debug the line "If Sheets("MSTR").Range("AD" & i).Value = 1 Then" from below is highlighted. Any idea what's triggering the error?


Sub SheetPrep4()
Application.ScreenUpdating = False
Dim LR As Integer, i As Integer, CBLR As Integer
LR = Sheets("MSTR").Range("N" & Rows.Count).End(xlUp).Row
Sheets("MSTR").Select
Range("X4:AD4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("X3:AD3").Select
Selection.AutoFill Destination:=Range("X3:AD" & LR)
Sheets("Catalog Buys").Select
Range("A2:R2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Sheets("MTS").Select
Range("A2:T2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Sheets("PV").Select
Range("A2:U2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
For i = 3 To LR
If Sheets("MSTR").Range("AD" & i).Value = 1 Then
Sheets("MSTR").Range("C" & i).Resize(, 12).Copy
Sheets("Catalog Buys").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Sheets("MSTR").Range("Q" & i).Resize(, 6).Copy
Sheets("Catalog Buys").Range("M" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
ElseIf Sheets("MSTR").Range("AC" & i).Value = 1 Then
Sheets("MSTR").Range("C" & i).Resize(, 20).Copy
Sheets("MTS").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
ElseIf Sheets("MSTR").Range("AA" & i).Value = 1 Then
Sheets("MSTR").Range("C" & i).Resize(, 21).Copy
Sheets("PV").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End If
Next i
CBLR = Sheets("Catalog Buys").Range("A" & Rows.Count).End(xlUp).Row
If CBLR > 1 Then
Sheets("Catalog Buys").Select
Range("S3:AA3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("S2:AA2").Select
Selection.AutoFill Destination:=Range("S2:AA" & CBLR)
End If
End Sub

 

[Marcelo Branco]

Hi,

Trying to guess...
Possibly you have some cells with errors in column AD.

Maybe this

For i = 3 To LR
If Not IsError(Sheets("MSTR").Range("AD" & i).Value) Then

If Sheets("MSTR").Range("AD" & i).Value = 1 Then
Sheets("MSTR").Range("C" & i).Resize(, 12).Copy
Sheets("Catalog Buys").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
Sheets("MSTR").Range("Q" & i).Resize(, 6).Copy
Sheets("Catalog Buys").Range("M" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
ElseIf Sheets("MSTR").Range("AC" & i).Value = 1 Then
Sheets("MSTR").Range("C" & i).Resize(, 20).Copy
Sheets("MTS").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
ElseIf Sheets("MSTR").Range("AA" & i).Value = 1 Then
Sheets("MSTR").Range("C" & i).Resize(, 21).Copy
Sheets("PV").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
End If

End If
Next i

HTH

M.

 

[briandpearson]

Thanks, Marcello. Took a look at my data pull and it looks like you're right, a couple cells were erroring out. Appreciate your help!