Run-time error 91 - object variable or with block variable not set

[bicke]

Hello Excel community!

I'm hoping you can help me with a macro which I've inherited in an excel model. Essentially the macro picks up any issues/text input in columns A or B in active worksheets and summarizes these problems in an issue log tab. About a week ago I started toe receive the 'Run-time error 91 - object variable or with block variable not set' error when running the macro and need help understanding why. The VBA text is below and the error is in this line item
iLastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Code below:

Sub Issues_Log_Update()

Dim nArray() As Variant
Dim nSheet As Variant
Dim nStyle As Variant
Dim nCounter As Single
Dim nCounter2 As Integer
Dim nRow As Integer
Dim nCategory As String
Dim nCell As Variant
Dim nCell2 As Variant
Dim nSheetName As String
Dim Answer As Variant
Dim UserCalcPref As Variant
Dim iLastRow As Integer


'Log users preference of calculation style and change to manual
UserCalcPref = Application.Calculation
Application.Calculation = xlCalculationManual


'MsgBox ("Running this macro will delete all data in this sheet, is that ok?",Buttons= vbOKCancel)
Answer = MsgBox("Running this macro will delete existing data in this sheet and repopulate, is that ok?", _
vbYesNo + vbQuestion, "Comments Macro")
    If Answer = vbYes Then
    Else
        GoTo Nend
    End If


Application.ScreenUpdating = False


'Reset all Autofilters
    Selection.AutoFilter Field:=1
    Selection.AutoFilter Field:=2
    Selection.AutoFilter Field:=3
    Selection.AutoFilter Field:=4
    Selection.AutoFilter Field:=5
    
'Clear the previous Comments
Range("Issues_WIPComments").ClearContents


'Size the Array
For Each nSheet In ThisWorkbook.Sheets
    nSheet.Activate
            
   'Test if the current worksheet is not a worksheet e.g. a chart then go to the next worksheet
        If nSheet.Type <> xlWorksheet Then
            ActiveSheet.Next.Select


        Else
            Range("A1").Select
            iLastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            'ActiveCell.SpecialCells(xlLastCell).Select
            Cells(iLastRow, 1).Select
            Range(ActiveCell, Cells(1, 1)).Select


            For Each nCell In Selection
                nCounter = nCounter + 1
                
                For Each nCell2 In Range("Issues_Type").Cells
                    If nCell.Style = Trim(nCell2.Value) Then
                    Else
                    End If
                Next nCell2
            Next nCell
            
            Cells(1, 1).Select
        End If
        
Next nSheet


ReDim nArray(nCounter, 4)
nCounter = 0


'Populate the Array
For Each nSheet In ThisWorkbook.Sheets
    nSheet.Activate
    
            
    'Test if the current worksheet is not a worksheet e.g. a chart then go to the next worksheet
        If nSheet.Type <> xlWorksheet Then
            ActiveSheet.Next.Select


        Else
    iLastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    'ActiveCell.SpecialCells(xlLastCell).Select
    Cells(iLastRow, 1).Select
    Range(ActiveCell, Cells(1, 1)).Select


        For Each nCell In Selection
            For Each nCell2 In Range("Issues_Type").Cells
                If (nCell.Style = Trim(nCell2.Value) And nCell.Style <> "") Then
                    nCounter = nCounter + 1
                    
                    nRow = nCell.Row
                    nCategory = nCell.Text
                    nSheetName = ActiveSheet.Name
                    nStyle = nCell.Style
                    
                    nArray(nCounter, 4) = nRow
                    nArray(nCounter, 2) = nCategory
                    nArray(nCounter, 3) = nSheetName
                    nArray(nCounter, 1) = nStyle


                Else
                End If
            Next nCell2
        Next nCell
        Cells(1, 1).Select
        
        End If
        
Next nSheet


'paste in sheet
Application.Goto Reference:="Issues_WIPMacroStart"


ActiveCell.Offset(1, 0).Select


Do While nCounter2 < nCounter
    nCounter2 = nCounter2 + 1
    If nCounter2 <> 1 Then ActiveCell.Offset(1, 0).Select
        
    ActiveCell.Offset(0, 0).Value = nArray(nCounter2, 1)
    ActiveCell.Offset(0, 1).Value = "='" & nArray(nCounter2, 3) & "'!A" & nArray(nCounter2, 4)
    ActiveCell.Offset(0, 2).Value = nArray(nCounter2, 3)
    ActiveCell.Offset(0, 3).Value = nArray(nCounter2, 4)
    ActiveCell.Offset(0, 4).Value = "='" & nArray(nCounter2, 3) & "'!B" & nArray(nCounter2, 4)
    
    ActiveCell.Offset(0, 4).Select
    ActiveCell.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    "'" & nArray(nCounter2, 3) & "'!B" & nArray(nCounter2, 4)
    Selection.Hyperlinks(1).ScreenTip = "click to follow... "


    'To stop followed hyperlink formatting
    Selection.Style = "Grid"
    
    ActiveCell.Offset(0, -4).Select


'Sleep (50) ' for a delay of 5 seconds.
Loop


'Date Time Stamp
Application.Goto Reference:="Issues_DateTimeStamp"
ActiveCell.Value = Now()


Application.Goto Reference:="Issues_WIPMacroStart"


ActiveCell.Offset(1, 0).Select
Application.Calculate


Nend:


'Reset Users preference of Calculation Style
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub

Thanks so much in advance for your help!
Cheers,
BI.

 

[Norie]

Which instance of this code causes the error?

iLastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 

[bicke]

Which instance of this code causes the error?

iLastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Hi Norie,

Thanks for your reply and good question as I'm not sure. I use F8 to cycle through the macro but I can't locate the instance where this goes wrong. Do you have any suggestions about how I can find this?

Thanks again!
Cheers,
Ben.

 

[Norie]

Ben

When you get the error you press Debug and the line of code that causes the error should be highlighted.

 

[bicke]

Thanks Norie. Sorry for the confusion. The debug highlights that entire line of code so I'm not sure exactly which part of the code is causing the problem.

 

[Norie]

That line appears twice in the code you posted, first when you size the array and then when you populate the array.

 

[bicke]

The error occurs on the first instance (when it sizes the array) i.e. under the code line:
Range("A1").Select