Excel 2016 - Getting a Run-time error'1004': Select method of range class failed...Formating the contents of a table

[Eamonn123]

here is the macro, any help would be very much appreciated

'
' Keyboard Shortcut: Ctrl+i
'
Columns("A:Y").Select
Columns("A:Y").EntireColumn.AutoFit
Columns("B:B").Select
Selection.ColumnWidth = 10.57
Columns("E:F").Select
Selection.Font.Bold = True
Selection.Style = "Comma"
Selection.NumberFormat = "_-* #,##0.0_-;-* #,##0.0_-;_-* ""-""??_-;_-@_-"
Selection.NumberFormat = "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-"
Selection.ColumnWidth = 11
Columns("E:E").Select
With Selection.Font
.Color = -16776961
.TintAndShade = 0
End With
With Selection.Font
.ThemeColor = xlThemeColorAccent5
.TintAndShade = -0.249977111117893
End With
Columns("F:F").Select
With Selection.Font
.Color = -16776961
.TintAndShade = 0
End With
Columns("I:I").Select
Selection.ColumnWidth = 4.14
Selection.ColumnWidth = 4.86
Columns("G:J").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("L:M").Select
Selection.ColumnWidth = 6.57
Selection.ColumnWidth = 5.57
Columns("N:O").Select
Selection.ColumnWidth = 9.43
Columns("N:N").Select
Selection.Style = "Comma"
Selection.NumberFormat = "_-* #,##0.0_-;-* #,##0.0_-;_-* ""-""??_-;_-@_-"
Columns("N:O").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Columns("T:T").Select
Selection.Style = "Comma"
Selection.NumberFormat = "_-* #,##0.0_-;-* #,##0.0_-;_-* ""-""??_-;_-@_-"
Selection.NumberFormat = "_-* #,##0_-;-* #,##0_-;_-* ""-""??_-;_-@_-"
Selection.ColumnWidth = 7.86
Range("Table2[[#Headers],[Cont Date]]").Select
Selection.End(xlDown).Select
Range(Selection, Selection.End(xlUp)).Select
Selection.TextToColumns Destination:=Range("Table2[[#Headers],[Cont Date]]") _
, DataType:=xlFixedWidth, FieldInfo:=Array(0, 3), TrailingMinusNumbers:= _
True

The Bolded line has the problem
Here is a snagit of the same 04.13.2016-22.59.22 - Eamonn999's library



Thank you in advance for any guidence on this

 

[JLGWhiz]

Not sure, but this might work

Table2[[#Headers],[Cont Date]].Select

The squared brackets indicate 'Range' so the original code would make Range(...) redundant. You might need quote marks around #Headers and Cont Date. I assume they are named ranges but that is not spelled out in the OP.

 

[Sam Clough]

I have a large (10MB) file for rankings of sport on Excel 2016. It has a "button" for various sortings. Out of the blue when using this button I get Run-time error '1004' To do this, all the merged cells need to be the same size. As I am not good on these macros, I need urgent help. Can I upload the file for someone to maybe assist. I am desperate.

 

[Eamonn123]

Thanks for the suggestion however it gives me a compile error 04.14.2016-00.28.09 - Eamonn999's library

Not sure, but this might work

Table2[[#Headers],[Cont Date]].Select

The squared brackets indicate 'Range' so the original code would make Range(...) redundant. You might need quote marks around #Headers and Cont Date. I assume they are named ranges but that is not spelled out in the OP.

 

[Eamonn123]

Tried it with the quote marks as well and same compile error

 

[Sam Clough]

This is the macro
Option Explicit


Private Sub CommandButton1_Click()
'sort male name


ActiveSheet.Unprotect
Range("A7:AZZ3050").Sort _
Key1:=Range("A7"), Order1:=xlDescending, _
Key2:=Range("C7"), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells


End Sub


Private Sub CommandButton10_Click()
'male ranking


Sheet1.Unprotect
Range("A7:AZZ3050").Sort _
Key1:=Range("A7"), Order1:=xlDescending, _
Key2:=Range("F7"), Order2:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Sheet1.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheet1.EnableSelection = xlUnlockedCells


Sheet5.Select
UserForm1.Hide


End Sub


Private Sub CommandButton11_Click()


'female rankuing


Sheet1.Unprotect
Range("A7:AZZ3050").Sort _
Key1:=Range("A7"), Order1:=xlAscending, _
Key2:=Range("F7"), Order2:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Sheet1.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Sheet1.EnableSelection = xlUnlockedCells


Sheet6.Select
UserForm1.Hide


End Sub


Private Sub CommandButton2_Click()


'sort male points


ActiveSheet.Unprotect
Range("A7:AZZ3050").Sort _
Key1:=Range("A7"), Order1:=xlDescending, _
Key2:=Range("F7"), Order2:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells


End Sub


Private Sub CommandButton3_Click()


'sort male state


ActiveSheet.Unprotect
Range("A7:AZZ3050").Sort _
Key1:=Range("A7"), Order1:=xlDescending, _
Key2:=Range("E7"), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells


End Sub


Private Sub CommandButton4_Click()


'sort female name


ActiveSheet.Unprotect
Range("A7:AZZ3050").Sort _
Key1:=Range("A7"), Order1:=xlAscending, _
Key2:=Range("C7"), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub


Private Sub CommandButton5_Click()


'sort female points


ActiveSheet.Unprotect
Range("A7:AZZ3050").Sort _
Key1:=Range("A7"), Order1:=xlAscending, _
Key2:=Range("F7"), Order2:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells


End Sub


Private Sub CommandButton6_Click()


'sort female state


ActiveSheet.Unprotect
Range("A7:AZZ3050").Sort _
Key1:=Range("A7"), Order1:=xlAscending, _
Key2:=Range("E7"), Order2:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End Sub


Private Sub CommandButton7_Click()
'sort overall points


ActiveSheet.Unprotect
Range("A7:AZZ3050").Sort _
Key1:=Range("F7"), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells




End Sub


Private Sub CommandButton8_Click()


'sort overall names




ActiveSheet.Unprotect
Range("A7:AZZ3050").Sort _
Key1:=Range("C7"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells


End Sub


Private Sub CommandButton9_Click()


'sort overall state


ActiveSheet.Unprotect
Range("A7:AZZ3050").Sort _
Key1:=Range("E7"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells


End Sub

 

[JLGWhiz]

I have a large (10MB) file for rankings of sport on Excel 2016. It has a "button" for various sortings. Out of the blue when using this button I get Run-time error '1004' To do this, all the merged cells need to be the same size. As I am not good on these macros, I need urgent help. Can I upload the file for someone to maybe assist. I am desperate.

You need to start your own thread and maybe somebody will pick up on it. This thread deals with a different issue. The 1004 error code can be for various causes, usually related to user coding error like misspelled words, erroneous syntax, etc.

@Eamonn123, Sorry that didn't work. The problem appears to be that it is not recognizing the parent/child relationship of your table and ranges. You could probably get around that by using a variable to represent the range you want to address.
1. Manually select the range you are trying to select with code.
2. Open the vb Editor and under the Sub title line type the following

Set myVar = Selection

3. use myVar as the range reference

myVar.Select

instead of the Range(Table2...etc.
OR, once you have initialized the myVar variable, you can use message box to get the address

MsgBox myVar.Address

and use that address in the code instead of the Range(Table2...etc.
Once you have the correct Range address and it works. Delete the lines you used to get it. Those were just tools to get what you needed.

 

[JLGWhiz]

It occurred to me later last night that a simpler solution would be to turn on the macro recorder, select the range in question, then turn off the recorder. Open the vb Editor to the recorded macro and it should show you the correct syntax to use in your original code.