excel - Microsoft VBA 下标超出范围 - 错误 9

Question

我是 VBA 新手。我目前正在为我的论文研究创建一个表格。但我遇到了“下标超出范围”错误 9。它也没有在通知中显示“调试”选项。在我输入该代码之前一切正常。

谁能帮我解决或重做这个？这是代码：

Sub show_Sale_Available_Data()

Dim dsh As Worksheet
Dim sh As Worksheet

Set dsh = ThisWorkbook.Sheets("Sale_Available")

Set sh = ThisWorkbook.Sheets("Sale_Availabale_Display")

dsh.AutoFilterMode = False

dsh.Range("H:H").NumberFormat = "D-MMM-YYYY"

'''' filter here
dsh.UsedRange.AutoFilter 8, ">=" & Me.txt_Date_start.Value, xlAnd, "<=" & Me.txt_Date_End.Value

If Me.OptionButton2.Value = True Then
dsh.UsedRange.AutoFilter 3, "Add to Stocks"
End If

If Me.OptionButton7.Value = True Then
dsh.UsedRange.AutoFilter 3, "Sale"
End If

sh.UsedRange.Clear

dsh.UsedRange.Copy
sh.Range("A1").PasteSpecial xlpastevalueandnumberformats


''' show data in listbox

Dim lr As Long
lr = Application.WorksheetFunction.CountA(sh.Range("A:A"))

If lr = 1 Then lr = 2

With Me.ListBox2
    .ColumnCount = 8
    .ColumnHeads = True
    .ColumnWidths = "0,150,65,65,65,65,65,65"
    .RowSource = sh.Name & "!A2:H" & lr
End With


End Sub

标签： excelvbasubscript