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

 

[KentBurel]

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. ?‍

I’m a real rookie here. Can you please be more specific on how I’m improperly referencing ranges. I’d appreciate a recommendation on a tutorial that starts at the beginning. I’ve been using the MS Excel 2019 VBA and Macros book. But it makes lots of assumptions about the basic syntax of the language. I need to learn how to spot these kinds of errors myself. Thanks.

 

[Tigerexcel]

I’m a real rookie here. Can you please be more specific on how I’m improperly referencing ranges. I’d appreciate a recommendation on a tutorial that starts at the beginning. I’ve been using the MS Excel 2019 VBA and Macros book. But it makes lots of assumptions about the basic syntax of the language. I need to learn how to spot these kinds of errors myself. Thanks.

Good point Kent, I think one of the keys is to know what the errors are telling us so we can then try and solve the issue at hand.

 

[Zack Barresse]

Understood. It can be tricky, for sure. VBA is an old language, based on an even older subset of VB. It does not get updated save for incoming functionality - in other words, while the Object Model might stay current (...mostly), the language itself gets no love, as opposed to the Office JavaScript API, which is updated regularly (although not as robust as VBA).

Below is an example of what I mean by referencing, using your code. While I wouldn't use it, I'm glad it works for you.

Sub SortBMDMasterTable()
'
' 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 Sheet As Worksheet
    Dim firstRow As Long
    Dim lastRow As Long
    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
    Set Sheet = Worksheets("BMD Master")

    lastRow = Sheet.Cells(Sheet.Rows.Count, 1).End(xlUp).Row
    Set myRange = Sheet.Range("A" & firstRow & ":F" & lastRow)
    On Error Resume Next
    Sheet.Names("BMDData").Delete    ' Delete the "BMDData" named range
    On Error GoTo 0
    myRange.Name = "BMDData"    ' Add the "BMDData" back
    Set myRange2 = Application.Union(Sheet.Range("BMDHeader"), myRange)
    myRange2.Name = "myRange2"
    Set myColumn1 = myRange.Resize(, 1)
    Set myColumn2 = myRange.Offset(, 1).Resize(, 1)

    Sheet.Sort.SortFields.Clear
    Sheet.Sort.SortFields.Add2 Key:=myColumn1, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    Sheet.Sort.SortFields.Add2 Key:=myColumn2, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    Sheet.Sort.SetRange myRange2
    Sheet.Sort.Header = xlYes
    Sheet.Sort.MatchCase = False
    Sheet.Sort.Orientation = xlTopToBottom
    Sheet.Sort.Apply

End Sub

In the above code you can see how I referenced a worksheet via a variable. This ensures your code doesn't assume the Range/Cells object utilize the ActiveSheet as the worksheet in reference.

All of this aside, if you're utilizing Tables, I wouldn't use the Range object, I'd use the ListObject object. Aside from that, I would recommend not using constant variables like you have with the first row of data.

The book you have is good. For long-term (code) learning, I would recommend getting fairly well acquainted with Object Oriented Programming (OOP), which VBA is, one of many other languages. Once you understand the principles and practices of OOP, things will make sense much more.[/code]

 

[KentBurel]

Understood. It can be tricky, for sure. VBA is an old language, based on an even older subset of VB. It does not get updated save for incoming functionality - in other words, while the Object Model might stay current (...mostly), the language itself gets no love, as opposed to the Office JavaScript API, which is updated regularly (although not as robust as VBA).

Below is an example of what I mean by referencing, using your code. While I wouldn't use it, I'm glad it works for you.

Sub SortBMDMasterTable()
'
' 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 Sheet As Worksheet
    Dim firstRow As Long
    Dim lastRow As Long
    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
    Set Sheet = Worksheets("BMD Master")

    lastRow = Sheet.Cells(Sheet.Rows.Count, 1).End(xlUp).Row
    Set myRange = Sheet.Range("A" & firstRow & ":F" & lastRow)
    On Error Resume Next
    Sheet.Names("BMDData").Delete    ' Delete the "BMDData" named range
    On Error GoTo 0
    myRange.Name = "BMDData"    ' Add the "BMDData" back
    Set myRange2 = Application.Union(Sheet.Range("BMDHeader"), myRange)
    myRange2.Name = "myRange2"
    Set myColumn1 = myRange.Resize(, 1)
    Set myColumn2 = myRange.Offset(, 1).Resize(, 1)

    Sheet.Sort.SortFields.Clear
    Sheet.Sort.SortFields.Add2 Key:=myColumn1, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    Sheet.Sort.SortFields.Add2 Key:=myColumn2, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    Sheet.Sort.SetRange myRange2
    Sheet.Sort.Header = xlYes
    Sheet.Sort.MatchCase = False
    Sheet.Sort.Orientation = xlTopToBottom
    Sheet.Sort.Apply

End Sub

In the above code you can see how I referenced a worksheet via a variable. This ensures your code doesn't assume the Range/Cells object utilize the ActiveSheet as the worksheet in reference.

All of this aside, if you're utilizing Tables, I wouldn't use the Range object, I'd use the ListObject object. Aside from that, I would recommend not using constant variables like you have with the first row of data.

The book you have is good. For long-term (code) learning, I would recommend getting fairly well acquainted with Object Oriented Programming (OOP), which VBA is, one of many other languages. Once you understand the principles and practices of OOP, things will make sense much more.[/code]

I'm familiar with OO languages via my programming in Java. I posted here a question about building a PageSetup object one time and then assigning that to each worksheet as I created them. I was told on this forum that VBA is not that flexible. It's not a fully OO language. So I'm struggling with what it will actually do. I'm old so I prefer to read paper when I'm learning a new language. Thank you very much for your expertise and willingness to help me.

 

[Zack Barresse]

I very much hear you. It's a love/hate language for sure. Not nearly as robust as Java. One problem in finding what "good" Excel VBA is there are so many people who want to help, all having very different knowledge/experience backgrounds, and with Excel/VBA being so prolific and available, you end up with many different solutions/tips, some better and some worse. Ends up being kind of a crap shoot at times.

Regarding your PageSetup post, Jan Karel is right - create the first sheet and set the PageSetup properties, then copy that sheet for all other sheets. The only other (viable) option I can think of would be to create a custom class with a method to get/set setups, then apply it as you create a sheet, but this is going to be less efficient as setting it once and then reusing it.

 

[KentBurel]

Thank you.