Forum Discussion
VBA: runtime error 1004 : Method range of object - 'Global' failed
Hello everyone, I have this error in my code (red part). I guess it´s a just a little mistake, but I can´t find it. Maybe someone can help me.
It is made on CommandButton for sort by two keys and I remake it from my professor's code (and change name of lists ect.) and insert some condions, but it doesn't work.
Where cmbx_klic1 is ComboBox for first key and cmbx_klic2 is ComboBox for second key for names by what you can sort it. And optb_vzest_1 is OptionButton for ascending for first key and optb_vzest_2 for ascending for second key. The last is check_razeni - CheckBox for not haveing one key for sorting, but two keys. And the list have really the same name as the part of the list.
Here the Code so far:
Private Sub cmdb_razeni_Click()
Dim smer1 As Byte, smer2 As Byte
If optb_vzest_1 Then
smer1 = xlAscending
Else
smer1 = xlDescending
End If
If optb_vzest_2 Then
smer2 = xlAscending
Else
smer2 = xlDescending
End If
Sheets("data").Select
Range("C6").Select
ActiveWorkbook.Worksheets("data").Sort.SortFields.Clear
If check_razeni = False Then
ActiveWorkbook.Worksheets("data").Sort.SortFields.Add Key:=Range(cmbx_klic1), _
SortOn:=xlSortOnValues, Order:=smer1, DataOption:=xlSortNormal
Range(cmbx_klic1).Select
Else
ActiveWorkbook.Worksheets("data").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("data").Sort.SortFields.Add Key:=Range(cmbx_klic1), _
SortOn:=xlSortOnValues, Order:=smer1, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("data").Sort.SortFields.Add Key:=Range(cmbx_klic2), _
SortOn:=xlSortOnValues, Order:=smer2, DataOption:=xlSortNormal
End If
If cmbx_klic1 = cmbx_klic2 Then
MsgBox "Oba zadané klíče jsou shodné. Zvolte odlišné.": Exit Sub
End If
With ActiveWorkbook.Worksheets("data").Sort
.SetRange Range("data")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range(cmbx_klic1).Select
frm_menu.Hide
End Sub
Thanks a lot to everyone, who will try to help me.
6 Replies
I am getting same error in my below code. Can someone help on priority??
Sub Extract_Output()
Windows("Mailer - 05-06-21.xlsm").Activate
Sheets("Sheet2").Select
Range("A1").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
'Option Explicit
Dim cell As Range
Dim Table As Range
Dim cutpath As String
cutpath = ActiveWorkbook.path & "\"
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each cell In Range("RM")
[valRM] = cell.Value
Range("a1:ah20000").AdvancedFilter Action:=xlFilterCopy, criteriarange:=Range("Criteria"), CopyToRange:=Range("Extract"), Unique:=False
Range(Range("Extract"), Range("Extract").End(xlDown)).Copy
Workbooks.AddRange(cmbx_klic1) refers to the active worksheet. If data is not the active sheet when you run the code, this causes a discrepancy. So try changing Range(cmbx_klic1) to Worksheets("data").Range(cmbx_klic1) and similar for Range(cmbx_klic2)
Thank you, but now there is Run-time error '1004': Application-defined or object-defined error in the same area. Do you possibly have any other ideas where the mistake could be?
Does this work?
Private Sub cmdb_razeni_Click() Dim w As Worksheet Dim smer1 As Byte, smer2 As Byte If cmbx_klic1 = cmbx_klic2 Then MsgBox "Oba zadané klí?e jsou shodné. Zvolte odlišné." Exit Sub End If If optb_vzest_1 Then smer1 = xlAscending Else smer1 = xlDescending End If If optb_vzest_2 Then smer2 = xlAscending Else smer2 = xlDescending End If Set w = Worksheets("data") With w.Range("data") If check_razeni = False Then .Sort Key1:=w.Range(cmbx_klic1), Order1:=smer1, Header:=xlYes Else .Sort Key1:=w.Range(cmbx_klic1), Order1:=smer1, _ Key2:=w.Range(cmbx_klic2), Order2:=smer2, Header:=xlYes End If End With frm_menu.Hide End Sub
