Run-Time error '91'

D

dshafique

Board Regular
Joined
Jun 19, 2017
Messages
171
Hi guys, so i keep getting an error saying "Object variable or with block variable not set"
the code used to work perfectly before i added something
what i added was
Code: 
 Set rng = Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
    tbl.Name = "TableAll"
    tbl.TableStyle = "TableStyleLight8"
    For Each cell In Range("TableAll")
        If IsEmpty(cell) Then
            ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Value = "No Data"
        End If
    Next

now when i run the program, i get an error here
Code: 
myHdr = "Status"
With ActiveSheet.ListObjects("TableAll").Range
   myField = .Find(myHdr).Column + 1 - .Range("A1").Column
    .AutoFilter Field:=myField, Criteria1:="Payment"
End With

here is the whole code if that helps

Code: 
Sub sort()
'
' sort Macro
' Daoud Shafique
' 7/25/17
' Revision 10


 
    Dim MyRange As Range
    Dim xColIndex As Integer
    Dim xRowIndex As Integer
    Dim myField As Long, myHdr As String
    Dim cell As Range
    Dim tbl As ListObject
    Dim rng As Range
    
    Sheets("Availability by application").Select
    Range("A1").Select
   ' ActiveSheet.ListObjects.Add(xlSrcRange, Range([A1].End(xlDown), [A1].End(xlToRight)), , xlYes).Name = "TableAll"
    Set rng = Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
    tbl.Name = "TableAll"
    tbl.TableStyle = "TableStyleLight8"
    For Each cell In Range("TableAll")
        If IsEmpty(cell) Then
            ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Value = "No Data"
        End If
    Next
    
    Range("TableAll[[#Headers],[App Cat Id]]").Select
    ActiveCell.Offset(1).Select
    xIndex = Application.ActiveCell.Column
    xRowIndex = Application.ActiveSheet.Cells(Rows.Count, xIndex).End(xlUp).Row
    Range(Cells(2, xIndex), Cells(xRowIndex, xIndex)).Select
        With Selection
            .NumberFormat = "0"
            .Value = .Value
        End With
    
    Sheets("prior month").Select
    Range("A1").Select
   ' ActiveSheet.ListObjects.Add(xlSrcRange, Range([A1].End(xlDown), [A1].End(xlToRight)), , xlYes).Name = "TablePrior"
    Set rng = Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
    tbl.Name = "TablePrior"
    tbl.TableStyle = "TableStyleLight8"
    For Each cell In Range("TablePrior")
        If IsEmpty(cell) Then
            ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Value = "No Data"
        End If
    Next
   
    Range("TablePrior[[#Headers],[App Cat Id]]").Select
    ActiveCell.Offset(1).Select
    xIndex = Application.ActiveCell.Column
    xRowIndex = Application.ActiveSheet.Cells(Rows.Count, xIndex).End(xlUp).Row
    Range(Cells(2, xIndex), Cells(xRowIndex, xIndex)).Select
        With Selection
            .NumberFormat = "0"
            .Value = .Value
        End With
    
    
    
    Sheets("Availability by application").Select
    Range("TableAll[[#Headers],[Fiscal YTD]]").Select
    ActiveCell.Offset(1, 1).Select
    Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select
     
    
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
       , Formula1:="=$E2"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5287936
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
        Formula1:="=$E2"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 192
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    Selection.FormatConditions.Add Type:=xlTextString, String:="No Data", _
        TextOperator:=xlContains
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    
    Range("TableAll[[#Headers],[SLA Target]]").Select
    ActiveCell.Offset(1).Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = 2
        .Color = 12611584
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Font.Bold = True
    
    
    Range("TableAll[[#Headers],[Fiscal YTD]]").Select
    ActiveCell.Offset(1).Select
    Selection.Copy
    Sheets("prior month").Select
   
    Range("TablePrior[[#Headers],[Fiscal YTD]]").Select
    ActiveCell.Offset(1).Select
    Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select
    Selection.PasteSpecial Paste:=xlPasteFormats
    
    Application.CutCopyMode = False


   
    Sheets("Availability by application").Select
    Range("TableAll[[#Headers],[SLA Target]]").Select
    ActiveCell.Offset(1).Select
    Selection.Copy
    Sheets("prior month").Select
    Range("TablePrior[[#Headers],[SLA Target]]").Select
    ActiveCell.Offset(1).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteFormats
    
 
    Worksheets.Add(After:=Worksheets(Sheets.Count)).Name = "New Apps"
    
    Sheets("Availability by application").Select
    Range("TableAll[[#Headers],[Application Name]]").Select
    Range("TableAll[#All]").Select
    Selection.Copy
    Sheets("New Apps").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "keep"
    ActiveCell.Offset(1).Select
    ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP([@[App Cat Id]],TablePrior[[App Cat Id]:[App Cat Id]],1,0)), ""Keep"", ""delete"")"
    myHdr = "keep"
With ActiveSheet.ListObjects("TableAll5").Range
    myField = .Find(myHdr).Column + 1 - .Range("A1").Column
    .AutoFilter Field:=myField, Criteria1:="delete"
End With
    Range("TableAll5[#Data]").Select
    Selection.EntireRow.Delete
    ActiveSheet.ListObjects("TableAll5").AutoFilter.ShowAllData
    Range("TableAll5[keep]").EntireColumn.Hidden = True
    Range("A1").Select
    Columns.AutoFit
    Worksheets.Add(After:=Worksheets(Sheets.Count)).Name = "Deleted Apps"
    
    Sheets("prior month").Select
    Range("TablePrior[#All]").Select
    Selection.Copy
    Sheets("Deleted Apps").Select
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "keep"
    ActiveCell.Offset(1).Select
    ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP([@[App Cat Id]],TableAll[[App Cat Id]:[App Cat Id]],1,0)), ""Keep"", ""delete"")"
    myHdr = "keep"
With ActiveSheet.ListObjects("TablePrior6").Range
    myField = .Find(myHdr).Column + 1 - .Range("A1").Column
    .AutoFilter Field:=myField, Criteria1:="delete"
End With
    Range("TablePrior6[#Data]").Select
    Selection.EntireRow.Delete
    ActiveSheet.ListObjects("TablePrior6").AutoFilter.ShowAllData
    Range("TablePrior6[keep]").EntireColumn.Hidden = True
    Range("A1").Select
    Columns.AutoFit
    
    
    
  
    
    
    Sheets("Availability by application").Select
    Range("A1").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "Status"
    ActiveCell.Offset(1).Select
    ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP([@[App Cat Id]],Apps[[App Cat Id]:[Application Name]],2,0)), ""Not Payment"", ""Payment"")"
    
    
     Sheets("prior month").Select
    Range("A1").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "Status"
    ActiveCell.Offset(1).Select
    ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP([@[App Cat Id]],Apps[[App Cat Id]:[Application Name]],2,0)), ""Not Payment"", ""Payment"")"
    
    
    Sheets("Availability by application").Select
    
    Worksheets.Add(After:=Worksheets(Sheets.Count)).Name = "Payment Apps"
    Worksheets.Add(After:=Worksheets(Sheets.Count)).Name = "New Payment Apps"
    Worksheets.Add(After:=Worksheets(Sheets.Count)).Name = "Deleted Payment Apps"
    
    
    Sheets("Availability by application").Select
    
    
    myHdr = "Status"
With ActiveSheet.ListObjects("TableAll").Range
   myField = .Find(myHdr).Column + 1 - .Range("A1").Column
    .AutoFilter Field:=myField, Criteria1:="Payment"
End With


Range("TableAll[#All]").Select
    Selection.Copy
    Sheets("Payment Apps").Select
    Range("A1").Select
    ActiveSheet.Paste
    Columns.AutoFit
    
    Sheets("Payment Apps").Select
     Range("A1").Select
    Set rng = Range(Range("A1"), Range("A1").SpecialCells(xlLastCell))
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
    tbl.Name = "PayAll"
    tbl.TableStyle = "TableStyleLight10"
    


    
    Sheets("Availability by application").Select
     myHdr = "Status"
With ActiveSheet.ListObjects("TableAll").Range
    myField = .Find(myHdr).Column + 1 - .Range("A1").Column
    .AutoFilter Field:=myField, Criteria1:="Payment"
End With
    Range("TableAll[#All]").Select
    Selection.Copy
    Sheets("New Payment Apps").Select
    Range("A1").Select
    ActiveSheet.Paste
    Columns.AutoFit
   Range("A1").Select
   Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select
    Set rng = Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column))
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
    tbl.Name = "PayNew"
    tbl.TableStyle = "TableStyleLight10"
    Range("B1").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "keep"
    ActiveCell.Offset(1).Select
Selection.FormulaArray = _
        "=IFERROR(IF(ISERROR(INDEX(TablePrior,MATCH(1,(TablePrior[[App Cat Id]:[App Cat Id]]=[@[App Cat Id]])*(TablePrior[[Status]:[Status]]=[@Status]),0),2)),""keep"",""delete""),""error"")"
   myHdr = "keep"
With ActiveSheet.ListObjects("PayNew").Range
    myField = .Find(myHdr).Column + 1 - .Range("A1").Column
    .AutoFilter Field:=myField, Criteria1:="delete"
End With
    Range("PayNew[#Data]").Select
    Selection.EntireRow.Delete
    ActiveSheet.ListObjects("PayNew").AutoFilter.ShowAllData
    Range("A1").Select
    
    
    
    Sheets("prior month").Select
  
     myHdr = "Status"
With ActiveSheet.ListObjects("TablePrior").Range
    myField = .Find(myHdr).Column + 1 - .Range("A1").Column
    .AutoFilter Field:=myField, Criteria1:="Payment"
End With
    Range("TablePrior[#All]").Select
    Selection.Copy
    Sheets("Deleted Payment Apps").Select
    Range("A1").Select
    ActiveSheet.Paste
    Columns.AutoFit
    Range("A1").Select
    Set rng = Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column))
    Set tbl = ActiveSheet.ListObjects.Add(xlSrcRange, rng, , xlYes)
    tbl.Name = "PayDeleted"
    tbl.TableStyle = "TableStyleLight10"
    
    Range("B1").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "keep"
    ActiveCell.Offset(1).Select
Selection.FormulaArray = _
        "=IFERROR(IF(ISERROR(INDEX(TableAll,MATCH(1,(TableAll[[App Cat Id]:[App Cat Id]]=[@[App Cat Id]])*(TableAll[[Status]:[Status]]=[@Status]),0),2)),""keep"",""delete""),""error"")"
   myHdr = "keep"
With ActiveSheet.ListObjects("PayDeleted").Range
    myField = .Find(myHdr).Column + 1 - .Range("A1").Column
    .AutoFilter Field:=myField, Criteria1:="delete"
End With
    Range("PayDeleted[#Data]").Select
    Selection.EntireRow.Delete
    ActiveSheet.ListObjects("PayDeleted").AutoFilter.ShowAllData
    Range("A1").Select
    
    
    
    
    
    
    
    Range("A1").Select
    Sheets("prior month").Select
    ActiveSheet.ListObjects("TablePrior").AutoFilter.ShowAllData
    Range("A1").Select
    Sheets("Availability by application").Select
    Range("TableAll[Status]").EntireColumn.Hidden = True
    Range("TablePrior[Status]").EntireColumn.Hidden = True
    Range("PayAll[Status]").EntireColumn.Hidden = True
    Range("PayNew[Status]").EntireColumn.Hidden = True
    Range("PayNew[keep]").EntireColumn.Hidden = True
    Range("PayDeleted[Status]").EntireColumn.Hidden = True
    Range("PayDeleted[keep]").EntireColumn.Hidden = True


    Sheets("prior month").Select
    Range("A1").Select
    Sheets("New Apps").Select
    Range("A1").Select
    Sheets("Deleted Apps").Select
    Range("A1").Select
    Sheets("New Payment Apps").Select
    Range("A1").Select
    Sheets("Deleted Payment Apps").Select
    Range("A1").Select
    Sheets("Availability by application").Select
     ActiveSheet.ListObjects("TableAll").AutoFilter.ShowAllData
    Range("A1").Select
    sFName = Application.GetSaveAsFilename
    If sFName <> "False" Then ActiveWorkbook.SaveAs sFName
    
End Sub
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
I have a question about 1 on your line.

myField = .Find(myHdr).Column + 1 - .Range("A1").Column

I'm trying to understand what is the purpose. You are adding 1 to the column and subtract the column of the A1 range, so 1.
In the end, that calculation have no purpose

After that, you are saving a number in your "myField" variable and then using it as a range for your autofilter.
 
Upvote 0
You must log in or register to reply here.

Similar threads

jski21
  • Question Question
Run Time Error 438
Replies
8
Views
409
Alex Blakenburg
A
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
400
willow1985
willow1985
E
  • Question Question
VBA causing time values to go to AM/PM
Replies
1
Views
524
eddm87
E
Knockoutpie
  • Solved
Run-Time error 91 (cannot find searched value) How to handle?
Replies
4
Views
807
Knockoutpie
Knockoutpie
P
  • Question Question
variable List creation
Replies
0
Views
461
Pheonix2332
P

Forum statistics

Threads
1,224,821
Messages
6,181,163
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top