Method 'Range' of object '_Global' failed

[KentBurel]

I'm getting an error 1004 Method 'Range' of object '_Global' failed. I have a spreadsheet that I use VLOOKUP on. So I name the range of the table and use it in formulas. But not I have to update that table. Rows might be added or deleted. So I have a macro to run after the table is updated that will update the Named ranges and also sort the table in order. I ran the macro recorder to capture the Data Sort commands. Then the macro has to be changed to used the name ranges instead of the fixed cell references created by the macro recorder. I created some temporary Names so I could verify that the column1 and column2 ranges were correct. I get the error message on the first ActiveWorkbook.Worksheets("BMD Master").Sort.SortFields.Add2 statement. Your help is greatly appreciated.

Option Explicit

Sub SortBMDMasterTable()an the f
'
' SortBMDMasterTable Macro
' This macro sorts the BMD master table.  The table must stay sorted because it is used by the VLOOKUP functions in the Precinct sheets to prefill the data.
'
    Dim firstRow As Integer, lastRow As Integer
    Dim myRange As Range
    Dim myRange2 As Range
    Dim myColumn1 As Range
    Dim myColumn2 As Range
    firstRow = 19    'Row 19 is the first line of the BMDMaster table
    ActiveWorkbook.Worksheets("BMD Master").Select
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    Set myRange = Range("A" & firstRow & ":F" & lastRow)
    On Error Resume Next
        Names("BMDData").Delete ' Delete the "BMDData" named range
    On Error GoTo 0
    myRange.Name = "BMDData" ' Add the "BMDData" back
    Set myRange2 = Application.Union(Range("BMDHeader"), myRange)
    myRange2.Name = "myRange2"
    Set myColumn1 = myRange.Resize(, 1)
    Set myColumn2 = myRange.Offset(, 1).Resize(, 1)
    
    ActiveWorkbook.Worksheets("BMD Master").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("BMD Master").Sort.SortFields.Add2 Key:=Range( _
        myColumn1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("BMD Master").Sort.SortFields.Add2 Key:=Range( _
        myColumn2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("BMD Master").Sort
        .SetRange Range("myRange2")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

 

[Zack Barresse]

What is 'myColumn1' defined as?

 

[Anthony47]

Which line fails? Which area the Name "BMDHeader" refers to?

 

[Zack Barresse]

Which line fails? Which area the Name "BMDHeader" refers to?

OP said...

I get the error message on the first ActiveWorkbook.Worksheets("BMD Master").Sort.SortFields.Add2 statement.

 

[Fluff]

What if you change .Add2 to .Add

 

[Zack Barresse]

That wouldn't make a difference @Fluff. The 'Add2' is only supportive of rich data types. Either should work with normal data types.

I didn't read through all the code though, and I see where the names are being defined in the routine (doh!). My guess is the range isn't being set properly. There are several issues with the code, however. The first row is hard-coded. The sort range is unclear. The worksheet isn't properly referenced. Variables are poorly named. Lastly, if a Table is being used here, there are no Table objects in use. All of these items make the code difficult to decipher, debug, and ultimately maintain.

@KentBurel Can you confirm any of the above information?

 

[Fluff]

If the OP is running the code on 2016 or later (and their profile shows 2019) then I agree the Add or Add2 should make no difference, but if it's being run on 2013 or earlier then Add2 doesn't exist.

 

[Fluff]

Just spotted it (I think) the sort Keys should be like

ActiveWorkbook.Worksheets("BMD Master").Sort.SortFields.Add2 Key:=myColumn1, _
   SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

 

[Zack Barresse]

Fair enough.

Several ranges are improperly being referenced. Without actually knowing what their data structure is like, or any of the other items I pointed out, it's impossible to diagnose. But a good shot in the dark never hurts in the absence of useful information. ?‍

 

[KentBurel]

Thanks Fluff. Changing all instances of Key:= worked. The macro run correctly now.

There’s something I’m not getting about this language. Where do I go to get a primer that starts from the beginning.