Runtime Error 91

[rtr1811]

I got runtime error 91 on executing the following code. Can anyone help me to resolve this error?



Option Explicit

Sub MergeSheets5()

Dim wsD As Worksheet
Dim ws As Worksheet

Set wsD = ThisWorkbook.Sheets("Wardwise")

'delete previous data
wsD.Range("B6:R10000").Clear

Dim data_lastrow As Long
Dim FoundCell As Range
Dim data_lastrow1 As Long


For Each ws In ThisWorkbook.Worksheets
If ws.Name = "Wardwise" Then
Else
'unmerge all cells
ws.Range("B8:R10000").UnMerge


'count the lastrow of each sheets
data_lastrow = wsD.Cells(Rows.Count, 4).End(xlUp).Row + 1

'Find net total as last row
Const WHAT_TO_FIND As String = "Net Total"
Set FoundCell = ws.Range("B:B").Find(What:=WHAT_TO_FIND)
data_lastrow1 = FoundCell.Row

'copy sheets into Destination sheet
ws.Range("C8:Q" & data_lastrow1).Copy Destination:=wsD.Range("D" & data_lastrow)
ws.Range("B8:B" & data_lastrow1).Copy Destination:=wsD.Range("B" & data_lastrow)

wsD.Range("C" & data_lastrow & ":C" & data_lastrow + data_lastrow1 - 8).Value = ws.Name

End If
Next ws

data_lastrow = wsD.Cells(Rows.Count, 3).End(xlUp).Row + 1

'remove row contains TOTAL
Dim d As Long 'row number
d = 6
Do Until d = data_lastrow 'loop through each row
If wsD.Cells(d, 2).Value Like "*" & "Total" & "*" Then
wsD.Rows(d).EntireRow.Delete
Else
d = d + 1
End If
Loop

'refresh last row of destination sheet
wsD.Range("B5:R" & data_lastrow).Sort Key1:=Range("C5"), Order1:=xlAscending, Header:=xlYes
wsD.Range("B5:R" & data_lastrow).Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlYes
wsD.Range("B5:R" & data_lastrow).Sort Key1:=Range("D5"), Order1:=xlAscending, Header:=xlYes

wsD.AutoFilterMode = False

Set wsD = Nothing
Set ws = Nothing

MsgBox "Done merged"

End Sub

 

[igold]

I don't see your data but if this range = nothing

Set FoundCell = ws.Range("B:B").Find(What:=WHAT_TO_FIND)

Then this row will throw an Error 91

data_lastrow1 = FoundCell.Row

 

[rtr1811]

I don't see your data but if this range = nothing

Set FoundCell = ws.Range("B:B").Find(What:=WHAT_TO_FIND)

Then this row will throw an Error 91

data_lastrow1 = FoundCell.Row

Shall I share you the Excel file? How to share the file?

 

[igold]

No need to do that. What line of code is highlighted if you click on debug when you get the error message...

 

[rtr1811]

Runtime Error 91 is thrown when it passes this line of code:

Set FoundCell = ws.Range("B:B").Find(What:=WHAT_TO_FIND)

 

[Alex Blakenburg]

As @igold has pointed out erroring on the next line is far more likley.
What happens if you add the code in blue to your code:

        'Find net total as last row
        Const WHAT_TO_FIND As String = "Net Total"
        Set FoundCell = ws.Range("B:B").Find(What:=WHAT_TO_FIND)
        
        If FoundCell Is Nothing Then
            MsgBox "The search term: " & WHAT_TO_FIND & "  was not found"
            Exit Sub
        End If
        
        data_lastrow1 = FoundCell.Row

 

[rtr1811]

As @igold has pointed out erroring on the next line is far more likley.
What happens if you add the code in blue to your code:

        'Find net total as last row
        Const WHAT_TO_FIND As String = "Net Total"
        Set FoundCell = ws.Range("B:B").Find(What:=WHAT_TO_FIND)
       
        If FoundCell Is Nothing Then
            MsgBox "The search term: " & WHAT_TO_FIND & "  was not found"
            Exit Sub
        End If
       
        data_lastrow1 = FoundCell.Row

Upon running the code above, the meesage box "Net Total" was not found is displayed.

 

[Alex Blakenburg]

Can you make Net Total the active cell. Take a screenshot showing the row and column references and include the formula box.
Also if in a cell you type =Len(select cell with Net Total) what does it return 9 or something else.

 

[rtr1811]

Thanks for the reply. Sorry for the delayed response from me. I was in a hectic schedule.
LEN() function Returns 9 only. Screenshot of Net Total

 

[Alex Blakenburg]

What happens if you change your Find line to this:

        Set FoundCell = ws.Range("B:B").Find(What:=WHAT_TO_FIND, MatchCase:=False)