Jan 29 2021 05:33 AM
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.
Jan 29 2021 07:52 AM
Range(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)
Jan 29 2021 12:49 PM
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?
Jan 29 2021 01:12 PM
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
Jan 31 2021 12:17 PM
No, it doesn´t work, there is still Run-time error '1004': Method 'Range' of object'_Worksheet' failed.
Can you think of anything else?
Jan 31 2021 01:48 PM
I'd have to see a copy of the workbook.
Jul 11 2021 11:05 PM - edited Jul 11 2021 11:07 PM
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.Add