VBA not formatting tables as intended

Question

Hello all I have inherited this VBA which is currently returning me two errors:

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

2) Run-time error '1004': Method 'Offset' of object 'Range' failed

The VBA is quite long, as mentioned my apologies as I inherited it.

Sub SortMain()
'
' SortMain Macro
' Sortingandcoloring
'

'
    Sheets("Key Performance Audience Metric").Select
    Range("B5:H5").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Key Performance Audience Metric").AutoFilter.Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Key Performance Audience Metric").AutoFilter.Sort. _
        SortFields.Add Key:=Range("C5:C55"), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Key Performance Audience Metric").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.AutoFilter
    Range("K5:O5").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Key Performance Audience Metric").AutoFilter.Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Key Performance Audience Metric").AutoFilter.Sort. _
        SortFields.Add Key:=Range("L5:L55"), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Key Performance Audience Metric").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.AutoFilter
    Range("R5:W5").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Key Performance Audience Metric").AutoFilter.Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Key Performance Audience Metric").AutoFilter.Sort. _
        SortFields.Add Key:=Range("S5:S55"), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Key Performance Audience Metric").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.AutoFilter
    Range("A1").Select
    Sheets("Engagement Quality Metrics").Select
    Range("B5:L5").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Engagement Quality Metrics").AutoFilter.Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Engagement Quality Metrics").AutoFilter.Sort. _
        SortFields.Add Key:=Range("C5:C54"), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Engagement Quality Metrics").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.AutoFilter
    Range("O5:W5").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Engagement Quality Metrics").AutoFilter.Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Engagement Quality Metrics").AutoFilter.Sort. _
        SortFields.Add Key:=Range("P5:P54"), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Engagement Quality Metrics").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.AutoFilter
    Range("Z5:AH5").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Engagement Quality Metrics").AutoFilter.Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Engagement Quality Metrics").AutoFilter.Sort. _
        SortFields.Add Key:=Range("AA5:AA54"), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Engagement Quality Metrics").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    Selection.AutoFilter
    End With

    Sheets("Video Views").Select
    Range("B5:D5").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Video Views").AutoFilter.Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Video Views").AutoFilter.Sort. _
        SortFields.Add Key:=Range("C5:C55"), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Video Views").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.AutoFilter
    Range("H5:J5").Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Video Views").AutoFilter.Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Video Views").AutoFilter.Sort. _
        SortFields.Add Key:=Range("I5:I55"), SortOn:=xlSortOnValues, Order:= _
        xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Video Views").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


    Sheets("Video Views").Select
    Range("B5:D6").Select
    Selection.Copy
    Range("B5:D55").Select
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Range("H5:J6").Select
    Selection.Copy
    Range("H5:J55").Select
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False


    Sheets("Key Performance Audience Metric").Select
    Range("B5:H6").Select
    Selection.Copy
    Range("B5:H55").Select
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Range("K5:O6").Select
    Selection.Copy
    Range("K5:O55").Select
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Range("R5:W6").Select
    Selection.Copy
    Range("R5:W55").Select
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("Key Performance Audience Metric").Select
    Range("A1").Select
    Sheets("Engagement Quality Metrics").Select
    Range("B5:L6").Select
    Selection.Copy
    Range("B5:L54").Select
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Range("O5:W6").Select
    Selection.Copy
    Range("O5:W54").Select
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Range("Z5:AH6").Select
    Selection.Copy
    Range("Z5:AH54").Select
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Range("A1").Select
    Sheets("MACROS").Select

 Sheets("Cross Platform Table").Select
    Range("A1").Select

Sheets("Circle Charts").Select
Dim i As Long
  i = Application.Intersect(Range("A:A"), ActiveSheet.UsedRange).End(xlDown).Row
  Do While Range("A1").Offset(i, 0).Value = 0
    Range("A1").Offset(i, 0).EntireRow.Delete xlShiftUp
    i = i - 1
  Loop

Sheets("Key Performance Audience Metric").Select
Dim j As Long
  j = Application.Intersect(Range("V:V"), ActiveSheet.UsedRange).End(xlDown).Row
  Do While Range("V1").Offset(j, 0).Value = 0
    Range("V1").Offset(j, 0).EntireRow.Delete xlShiftUp
    j = j - 1
  Loop

Sheets("Engagement Quality Metrics").Select
Dim k As Long
  k = Application.Intersect(Range("AJ:AJ"), ActiveSheet.UsedRange).End(xlDown).Row
  Do While Range("AJ1").Offset(k, 0).Value = 0
    Range("AJ1").Offset(k, 0).EntireRow.Delete xlShiftUp
    k = k - 1
  Loop

    Sheets("MACROS").Select
    Range("C8:D12").Select
    With Selection.Font
        .color = -11489280
        .TintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
    End With
    ExecuteExcel4Macro "PATTERNS(1,0,5287936,TRUE,2,3,0,0)"
    ActiveCell.FormulaR1C1 = "DONE! Ready to Use!"
    Range("A1").Select


End Sub

An alert pops up, it doesn't show me which line is the problem — gold_cy, Jan 09 '17 at 16:32
Start the sub by pressing `F8`, then by repeatedly pressing `F8` you can step onto the next line. Do this until the error occurs and you will know which line is causing problems. Macros generated entirely by the recorder (as this appears to be) can often be difficult to adapt, and should be used more as a prompt on how to achieve a specific task... — Wolfie, Jan 09 '17 at 16:40
I don't know what the equivalent shortcut is, but the functionality will still exist! In the VBA editor, go to the `Debug` menu, then `Step Into`. The shortcut should be next to the option in that list too... A quick google suggests the shortcut might be `cmd`+`shift`+`I` — Wolfie, Jan 09 '17 at 16:51
Unfortunately that is not working, nor do I have a `Debug` menu. — gold_cy, Jan 09 '17 at 16:56
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/132714/discussion-between-dmitry-polonskiy-and-wolfie). — gold_cy, Jan 09 '17 at 17:16

score 1 · Answer 1 · edited May 23 '17 at 12:24

You will find code much easier to diagnose and adapt if it is clearly written. I'm not going to go through that entire macro, but here is a sample of what the start of your code should look like! This spans from the first line of yours to the line Range("K5:O5").Select.

Dim KPAMSheet as Worksheet
Set KPAMSheet = Sheets("Key Performance Audience Metric")

With KPAMSheet.AutoFilter.Sort

    .SortFields.Clear

    .SortFields.Add Key:=KPAMSheet.Range("C5:C55"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal

    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply

End With

KPAMSheet.Range("B5:H5").AutoFilter

Note you should avoid using Select and Selection, it's going to bite you sooner or later! If you took half an hour to revise your code like the above, it would be much shorter, clearer and less prone to mistakes...

See this post: How to avoid using Select in Excel VBA macros

Also I'll repeat here my comment about how to debug:

Start the sub by pressing F8, then by repeatedly pressing F8 you can step onto the next line. Do this until the error occurs and you will know which line is causing problems. I believe the equivalent shortcut on Mac is Cmd+Shift+I. Alternatively go to the Debug menu and select Step Into.

I am getting a syntax error for this line you provided `.SortFields.Add Key:=KPAMSheet.Range("C5:C55") _ SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal ` — gold_cy, Jan 09 '17 at 17:08
I missed a comma before the line break, fixed now... note that I did no testing on the above code, it is just a first pass through how I would have tried to format it, I'm pretty unfamiliar with the `AutoFilter` function! Does the layout make sense? — Wolfie, Jan 09 '17 at 17:14
The layout makes sense, I will try and format it this way, many thanks! Unfortunately the VBA still has the errors I mentioned — gold_cy, Jan 09 '17 at 17:16

VBA not formatting tables as intended

1 Answers1