Blank Cell Out of Range Causing Run-time Error '91'

[EBexcel]

Hey all,
I'm hoping someone can help me with the issue I'm having. I have a macro that does a bunch of stuff on several tabs in an excel worksheet. One part of the macro creates 2 subtotals and then hightlights and bolds the total lines for each. The subtotal is grouped by column L and summed by column R. If the report only contains 1 subtotal, which is possible, it should just highlight and bold that one and keep going.

It works fine until I have a report that doesn't contain any values in column B. The odd part is, as long as any row in column B contains a value (ie: it doesn't matter if B4 has a value or B9 has a value), it works. If column B is blank, it gives me Run-time Error 91.

This is the specific line that is highlighted when I debug:
I = Range("L:L").Find("RECYCLE Total", Range("L1"), xlValues, xlWhole, xlByColumns, xlNext).Row

Here's my entire code up to that line:
Sub Diversion()

Dim mBook As String
Dim rBook As String
Dim REC As String
Dim COLA As String
Dim COLB As String
Dim COLC As String
Dim CNM As String
Dim TP As String
Dim MSWL As String
Dim RECL As String


Dim J As Integer
Dim LR As Long 'changed from integer
Dim LRR As Long 'changed from integer
Dim LRD As Long 'changed from integer
Dim LC As Integer
Dim Config As Integer
Dim Ans As Integer


'Turn off popup alerts/messages
Application.DisplayAlerts = False


'Input boxes asking for Customer name, time period of report, MSW & REC lbs
CNM = InputBox("Enter your customer name as you would like it to appear on the chart.", _
"NB_Diversion Report Template - Company Name", _
"Your Customer")
If CNM = "" Then
Exit Sub
End If


TP = InputBox("Enter the time period of the report as you would like it to appear on the chart.", _
"NB_Diversion Report Template - Time Period", _
"2015 YTD")
If TP = "" Then
Exit Sub
End If

RECL = InputBox("Enter the REC lbs.", _
"NB_Diversion Report Template - REC LBS", _
"50")
If RECL = "" Then
Exit Sub
End If

MSW = InputBox("Enter the MSW lbs.", _
"NB_Diversion Report Template - MSW LBS", _
"100")
If MSW = "" Then
Exit Sub
End If




'Ensure the macro starts on the right tab
Sheets("Diversion Detail").Activate

'If blank, do nothing
If Range("C4") = "" Then
'do nada
Else
'Delete all CORP account lines if they exist
X = Worksheets("Diversion Detail").Range("B:B").Cells.SpecialCells(xlCellTypeConstants).Count
'MsgBox (X)

If X > 1 Then
With ActiveSheet
.AutoFilterMode = False
With Range("B3", Range("B" & Rows.Count).End(xlUp))
.AutoFilter 1, "CORP"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
Else
'do nada
End If

'Identify last populated row
LR = ActiveWorkbook.Worksheets("Diversion Detail").Cells(3, 3).End(xlDown).Row

'Copy format down to the last row
ActiveWorkbook.Worksheets("Diversion Detail").Range("A4:W4").Copy
ActiveWorkbook.Worksheets("Diversion Detail").Range("A5:W" & LR).PasteSpecial xlPasteFormats

'Delete all extra rows at the bottom
Rows((LR + 1) & ":" & (LR + 1)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp

'Sort by Diversion Stream
ActiveWorkbook.Worksheets("Diversion Detail").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Diversion Detail").Sort.SortFields.Add Key:=Range( _
"L:L"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Diversion Detail").Sort
.SetRange Range("A3:W" & LR)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

'Add Subtotals
Range("A3:W" & LR).Select
'Range(Selection, Selection.End(xlDown)).Select
Selection.Subtotal GroupBy:=12, Function:=xlSum, TotalList:=Array(18), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

'Highlight and Bold Subtotal/Total rows if they exist
I = Range("L:L").Find("RECYCLE Total", Range("L1"), xlValues, xlWhole, xlByColumns, xlNext).Row


Any help would be greatly appreciated. I'm self taught so any technical lingo might be lost on me. Thank you!!!

 

[Stadem]

Try replacing the problematic line with this:

Dim DivWksht As Worksheet
Set DivWksht = ThisWorkbook.Worksheets("Diversion Detail")
Dim FoundCell As Range
Set FoundCell = DivWksht.Range("L:L").Find("RECYCLE Total", Range("L1"), xlValues, xlWhole, xlByColumns, xlNext)


If Not FoundCell Is Nothing Then
    I = FoundCell.Row
    MsgBox I
    
Else
    MsgBox "Still got a problem."
End If

Whenever you start a line with "Range", Excel has to guess what range you're talking about (I think it usually guesses ActiveSheet.Cells, but I might be wrong). This sometimes leads to voodoo like the problem you're describing. I replaced Range("L:L") with DivWksht.Range("L:L") to specify which worksheet to use.

In general, it's a good idea to avoid anything that deals with Activating or Selecting sheets and ranges. Dim a Range or Worksheet object (rRange or wsWksht), then Set it. Instead of using Range("A1") or Selection.Font.Bold, use wsWksht.Range("A1") or rRange.Font.Bold. As a fellow self-taught Excel VBA user, I struggled a lot with Ranges until I started doing it this way. This post has some good ideas:
http://www.mrexcel.com/forum/genera...her-questions/439054-short-code-versions.html

 

[EBexcel]

Thanks Stadem!!! That worked perfectly. Thanks again for the tips and sharing that post. I'll be sure to check it out for other helpful tips I can use. Much appreciated!

Try replacing the problematic line with this:

Dim DivWksht As Worksheet
Set DivWksht = ThisWorkbook.Worksheets("Diversion Detail")
Dim FoundCell As Range
Set FoundCell = DivWksht.Range("L:L").Find("RECYCLE Total", Range("L1"), xlValues, xlWhole, xlByColumns, xlNext)


If Not FoundCell Is Nothing Then
    I = FoundCell.Row
    MsgBox I
    
Else
    MsgBox "Still got a problem."
End If

Whenever you start a line with "Range", Excel has to guess what range you're talking about (I think it usually guesses ActiveSheet.Cells, but I might be wrong). This sometimes leads to voodoo like the problem you're describing. I replaced Range("L:L") with DivWksht.Range("L:L") to specify which worksheet to use.

In general, it's a good idea to avoid anything that deals with Activating or Selecting sheets and ranges. Dim a Range or Worksheet object (rRange or wsWksht), then Set it. Instead of using Range("A1") or Selection.Font.Bold, use wsWksht.Range("A1") or rRange.Font.Bold. As a fellow self-taught Excel VBA user, I struggled a lot with Ranges until I started doing it this way. This post has some good ideas:
http://www.mrexcel.com/forum/genera...her-questions/439054-short-code-versions.html