'Code runs twice, leads to Excel VBA Run code 1004 on Range methods
I have an Excel VBA form with a ComboBox, ListBox and 2 buttons. The ComboBox is populated from a list of departments on the sheet that opens the form. It works fine. The value of the ComboBox is then supposed to be used to filter a table on another sheet for only those personnel in that department. Here is the code:
Private Sub cmbDept_Change()
Dim data_sht As Worksheet, temp_sht As Worksheet
Dim dept As String
Dim data_add As String
Dim flt_column As Integer
Set data_sht = Worksheets("FullData")
Set temp_sht = Worksheets("Filter")
dept = Me.cmbDept.Value
data_add = data_sht.UsedRange.Address
flt_column = 1
Sheets("Filter").Range("A1:G200").Clear
Sheets("FullData").Range(data_add).AutoFilter Field:=flt_column, Criteria1:=dept, Operator:=xlFilterValues
Sheets("FullData").Range("A1").CurrentRegion.Offset(1).Copy temp_sht.Range("A2")
End Sub
The code goes through to the autofilter code, then goes back UP to Set data_sht line and runs it all again, unil it hits the first Range method, which fails with Run-time error 1004 (Range class method fail, in this case Clear, but if I get rid of that line the next line will fail with the same error).
I'm stumped as to why it's running this segment of code twice and why it fails the second go around only. I haven't found any other way to do a filter on 2-dimensional array, so this is all I could find.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
