Solved: If Range Is Nothing

[f2e4]

I have the following loop:


For x = 2 To FinalRowFE
ThisValue = Cells(x, 5).Value
If ThisValue = "FE" Then
Cells(x, 2).Resize(1, 11).Copy
NextRowFE = Cells(Rows.Count, 4).End(xlUp).Row + 1
Cells(NextRowFE, 2).Select
ActiveSheet.Paste
End If
Next x


However, occasionally this loop does not find anything (because there are no "FE" values) and NextRowFE comes up as Nothing.

I still need to use the variable NextRowFE so is there a way to add an if nothing statement in to prevent any errors popping up.

I have tried various ways of writing this but keep getting errors on the Is Nothing statement (type mismatch / object required) when there are no values found.


If Cells(x, 2).Resize(1, 11).Copy Is Nothing
NextRowFE = 12


OR


If NextRowFE Is Nothing
NextRowFE = 12

[Simon Lloyd]

You need to provide the rest of the code!

[f2e4]

Here is all of my code but not sure if it helps:

With Sheets("BTF")
.Select
lastactiverow = .Cells(.Rows.Count, "B").End(xlUp).Row
overallrow = lastactiverow + 1
'Add TOTALS
For g = 8 To 12 Step 1
Cells(overallrow, g).FormulaR1C1 = "=SUM(R" & 4 & "C" & g & ":R" & lastactiverow & "C" & g & ")"
Next g
Cells(overallrow, 4).FormulaR1C1 = "Total Projects"
Cells(overallrow, 5).FormulaR1C1 = "=COUNT(R" & 4 & "C" & 8 & ":R" & lastactiverow & "C" & 8 & ")"
Range(Cells(overallrow, 4), Cells(overallrow, 12)).Font.Bold = True
'MODIFY COLUMN WIDTHS AND SORT BY JOB NUMBER
Range("H:L").ColumnWidth = 13
Columns("C").ColumnWidth = 6.5
Columns("D").ColumnWidth = 26
Columns("E").ColumnWidth = 8
Columns("F").ColumnWidth = 10
Columns("G").ColumnWidth = 16.5
Range("B4", Cells(lastactiverow, 12)).Select
Selection.Sort Key1:=Range("C4"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers
'ADD AUTOFORMATTING
Range("B3", Cells(lastactiverow, 12)).Select
Selection.AutoFormat Format:=xlRangeAutoFormatList2, Number:=False, Font _
:=False, Alignment:=False, Border:=False, Pattern:=True, Width:=False
'ADD FE PROJECT LIST
FinalRowFE = Cells(Rows.Count, 4).End(xlUp).Row
For x = 2 To FinalRowFE
ThisValue = Cells(x, 5).Value
If ThisValue = "FE" Then
Cells(x, 2).Resize(1, 12).Copy
NextRowFE = Cells(Rows.Count, 4).End(xlUp).Row + 1
Cells(NextRowFE, 2).Select
ActiveSheet.Paste
End If
Next x
FinalRowFE2 = FinalRowFE + 2
FinalRowFE3 = FinalRowFE + 1
Range(Cells(FinalRowFE2, 2), Cells(NextRowFE, 12)).Select
Selection.Sort Key1:=Cells(FinalRowFE2, 3), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers
Range(Cells(FinalRowFE3, 2), Cells(NextRowFE, 12)).Select
Selection.AutoFormat Format:=xlRangeAutoFormatList2, Number:=False, Font _
:=False, Alignment:=False, Border:=False, Pattern:=True, Width:=False
' Rows(FinalRowFE3).EntireRow.Delete
' Rows(FinalRowFE3).EntireRow.Insert
'ADD FE TOTALS
FinalRowFE4 = NextRowFE + 1
For h = 8 To 12 Step 1
Cells(FinalRowFE4, h).FormulaR1C1 = "=SUM(R" & FinalRowFE2 & "C" & h & ":R" & NextRowFE & "C" & h & ")"
Next h
Cells(FinalRowFE4, 4).FormulaR1C1 = "Total Projects - FE Only"
Cells(FinalRowFE4, 5).FormulaR1C1 = "=COUNT(R" & FinalRowFE2 & "C" & 8 & ":R" & NextRowFE & "C" & 8 & ")"
Range(Cells(FinalRowFE4, 4), Cells(FinalRowFE4, 12)).Font.Bold = True
Columns("H:L").NumberFormat = "#,##0"
Range("A1").Select
Set lastactiverow = Nothing
Set FinalRowFE = Nothing
Set FinalRowFE2 = Nothing
Set FinalRowFE3 = Nothing
Set FinalRowFE4 = Nothing
Set FinalRowIDT = Nothing
Set FinalRowIDT2 = Nothing
Set FinalRowIDT3 = Nothing
Set FinalRowIDT4 = Nothing
Set NextRowFE = Nothing
Set NextRowIDT = Nothing
End With

[mikerickson]

The done with this code, NextRowFE will have a value of either the row where the data was pasted or 12.
NextRowFE = 12
For x = 2 To FinalRowFE
ThisValue = Cells(x, 5).Value
If ThisValue = "FE" Then
Cells(x, 2).Resize(1, 11).Copy
NextRowFE = Cells(Rows.Count, 4).End(xlUp).Row + 1
Cells(NextRowFE, 2).Select
ActiveSheet.Paste
End If
Next x

[f2e4]

The done with this code, NextRowFE will have a value of either the row where the data was pasted or 12.
NextRowFE = 12
For x = 2 To FinalRowFE
ThisValue = Cells(x, 5).Value
If ThisValue = "FE" Then
Cells(x, 2).Resize(1, 11).Copy
NextRowFE = Cells(Rows.Count, 4).End(xlUp).Row + 1
Cells(NextRowFE, 2).Select
ActiveSheet.Paste
End If
Next x


Thanks Mike - works perfectly