Run-time error '91': Object variable or With block variable not set.

[shina67]

Hi All,

I have started having a problem with the below part of some vba coding I have been running for some time now.

.Cells(i, "B") = Sheets("Employee List").Range("D:D").Find(.Cells(i, "A").Value, , xlValues, xlWhole).Offset(, 3)

The message I am getting is:-

Run-time error '91':
Object variable or With block variable not set.

I am hoping someone can help with this as I cannot seem to find a problem.

 

[Joe4]

I think you will get that error message if it cannot find the value you are looking for.
Can you confirm that the value you are looking for exists in the range you are searching?
It must match EXACTLY. Something as simple as an extra space will cause it not to match.

 

[shina67]

I think you will get that error message if it cannot find the value you are looking for.
Can you confirm that the value you are looking for exists in the range you are searching?
It must match EXACTLY. Something as simple as an extra space will cause it not to match.

Thanks for the above.

I still cannot seem to find the issue.

 

[Joe4]

Please post your ENTIRE procedure.
You are making reference to other variables and things in your code, but taken out of context, it is very hard for us to determine if you set all those correctly.

 

[RoryA]

When doing a Find operation you should specify all the arguments because they are retained between searches within one excel session (whether manual or coded). So for example if you used the MatchCase option in a manual find, that would affect your code because you haven't specified not to use it in the code.

 

[shina67]

Please post your ENTIRE procedure.
You are making reference to other variables and things in your code, but taken out of context, it is very hard for us to determine if you set all those correctly.

Hi Joe4,

The code as it is at the moment is as follows:-

Sub TidyUpDataForPivot()
    Dim i As Long
    Dim lr As Long
        
    'turn off screen updating to make it run faster
    Application.ScreenUpdating = False
    
    With Sheets("Employee Absences")
        'unmerge cells
        .UsedRange.MergeCells = False
        'find last row based on column Z
        lr = .Cells(.Rows.Count, "Z").End(xlUp).Row
        'delete the summary rows at end of data
        .Rows(lr + 1 & ":" & lr + 42).delete
        'delete blank rows at top of data
        .Rows("1:6").EntireRow.delete
        .Rows("2:4").EntireRow.delete
        'stamp Date in Z1 to give it a header
        .Range("Z1") = "Date"
        'loop through data rows and delete any blank rows based on columnn Z
        For i = lr To 2 Step -1
            If Not IsDate(.Cells(i, "Z")) Then .Rows(i).EntireRow.delete
        Next i
        'loop through columns and delete any without a header
        For i = .Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1
            If .Cells(1, i) = "" Then .Columns(i).EntireColumn.delete
        Next i
        'unmerge cells on Employee List sheet - the lookups in the next part won't work otherwise
        Sheets("Employee List").UsedRange.MergeCells = False
        'loop through each row of data to check if there's a value in column A
        For i = 2 To .Cells(Rows.Count, "F").End(xlUp).Row
            If .Cells(i, "A") <> "" Then
                'if a value exists in A then lookup the full name
                .Cells(i, "B") = Sheets("Employee List").Range("D:D").Find(.Cells(i, "A").Value, , xlValues, xlWhole).Offset(, 3)
            Else
                'if there's no value in A then copy the values from row above
                .Cells(i - 1, "A").Resize(2, 5).FillDown
            End If
        Next i
        'make the white text black so you can see all the data
        .UsedRange.Font.ColorIndex = xlAutomatic
        'resize the rows and columns to make it all look neat
        .Range("A:I").ColumnWidth = 100
        .Cells.EntireColumn.AutoFit
        .Cells.EntireRow.AutoFit
    End With
    
    'turn screen updating back on
    Application.ScreenUpdating = True

    
Range("I1").Select
    ActiveCell.FormulaR1C1 = "Area"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],'Staff Areas'!C[-8]:C[-7],2,FALSE)"
    Selection.AutoFill Destination:=Range("I2:I1342"), Type:=xlFillDefault
    Range("I2:I1342").Select
     Columns("C:E").Select
    Selection.delete Shift:=xlToLeft
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Month"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-3],""mmm"")"
    Selection.AutoFill Destination:=Range("G2:G1342")
    Range("G2:G1342").Select
   Worksheets("Archive").Activate
Range("a2:g50000").Copy
Worksheets("Employee Absences").Activate
Range("a2").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
    Range("A1:G1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Employee Absences").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Employee Absences").AutoFilter.Sort.SortFields.Add2 _
        Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Employee Absences").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
  
  Sheets("Employee Absences").Select
    Columns("A:G").Select
    Selection.Copy
    Sheets("Archive").Select
    Columns("A:A").Select
    ActiveSheet.Paste
    Sheets("Employee Absences").Select
    
    'turn screen updating back on
    Application.ScreenUpdating = True
    
    MsgBox "All done :)", , ""

End Sub

 

[Joe4]

First, take note of what Rory said.
Then, you could add error handling code to your code that will take you EXACTLY what value it cannot find.

I have added it here (see the new red lines in the code):

Sub TidyUpDataForPivot()
    Dim i As Long
    Dim lr As Long
        
    'turn off screen updating to make it run faster
    Application.ScreenUpdating = False
    
    With Sheets("Employee Absences")
        'unmerge cells
        .UsedRange.MergeCells = False
        'find last row based on column Z
        lr = .Cells(.Rows.Count, "Z").End(xlUp).Row
        'delete the summary rows at end of data
        .Rows(lr + 1 & ":" & lr + 42).Delete
        'delete blank rows at top of data
        .Rows("1:6").EntireRow.Delete
        .Rows("2:4").EntireRow.Delete
        'stamp Date in Z1 to give it a header
        .Range("Z1") = "Date"
        'loop through data rows and delete any blank rows based on columnn Z
        For i = lr To 2 Step -1
            If Not IsDate(.Cells(i, "Z")) Then .Rows(i).EntireRow.Delete
        Next i
        'loop through columns and delete any without a header
        For i = .Cells(1, Columns.Count).End(xlToLeft).Column To 1 Step -1
            If .Cells(1, i) = "" Then .Columns(i).EntireColumn.Delete
        Next i
        'unmerge cells on Employee List sheet - the lookups in the next part won't work otherwise
        Sheets("Employee List").UsedRange.MergeCells = False
        'loop through each row of data to check if there's a value in column A
        For i = 2 To .Cells(Rows.Count, "F").End(xlUp).Row
            If .Cells(i, "A") <> "" Then
                'if a value exists in A then lookup the full name
                On Error GoTo err_chk
                .Cells(i, "B") = Sheets("Employee List").Range("D:D").Find(.Cells(i, "A").Value, , xlValues, xlWhole).Offset(, 3)
                On Error GoTo 0
            Else
                'if there's no value in A then copy the values from row above
                .Cells(i - 1, "A").Resize(2, 5).FillDown
            End If
        Next i
        'make the white text black so you can see all the data
        .UsedRange.Font.ColorIndex = xlAutomatic
        'resize the rows and columns to make it all look neat
        .Range("A:I").ColumnWidth = 100
        .Cells.EntireColumn.AutoFit
        .Cells.EntireRow.AutoFit
    End With
    
    'turn screen updating back on
    Application.ScreenUpdating = True

    
Range("I1").Select
    ActiveCell.FormulaR1C1 = "Area"
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],'Staff Areas'!C[-8]:C[-7],2,FALSE)"
    Selection.AutoFill Destination:=Range("I2:I1342"), Type:=xlFillDefault
    Range("I2:I1342").Select
     Columns("C:E").Select
    Selection.Delete Shift:=xlToLeft
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "Month"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=TEXT(RC[-3],""mmm"")"
    Selection.AutoFill Destination:=Range("G2:G1342")
    Range("G2:G1342").Select
   Worksheets("Archive").Activate
Range("a2:g50000").Copy
Worksheets("Employee Absences").Activate
Range("a2").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
    Range("A1:G1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Employee Absences").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Employee Absences").AutoFilter.Sort.SortFields.Add2 _
        Key:=Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Employee Absences").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
  
  Sheets("Employee Absences").Select
    Columns("A:G").Select
    Selection.Copy
    Sheets("Archive").Select
    Columns("A:A").Select
    ActiveSheet.Paste
    Sheets("Employee Absences").Select
    
    'turn screen updating back on
    Application.ScreenUpdating = True
    
    MsgBox "All done :)", , ""
    
    Exit Sub
    
err_chk:
    If Err.Number = 91 Then
        MsgBox "Error looking for value in cell A" & i & vbCrLf & _
            "Cannot find " & Sheets("Employee Absences").Cells(i, "A").Value & " in column D on Employee List sheet", vbOKOnly, "ERROR!!!"
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If
    
End Sub