订阅超出范围(错误9)---运行调试器并浏览代码一切都很好

Question

基本上，使用此代码，该代码使用vlookups和匹配来查找特定产品的过去订单日期。如果在工作表中找不到过去的订单，则该子填充用户形式中的文本框。否则，找到最新订单并将信息填充在用户形式中。

当调用此子例程时，程序会停止。获取"订阅范围"（错误9），我运行调试器并浏览代码，一切都按应有的方式工作。在N/A情况下以及有过去的订单信息的情况下。

Sub PastOrderInfo()
Dim wks As Worksheet
Dim Date_Ordered As Variant
Dim PreviousDate As Variant
Dim Qty_Ordered As String
Dim Total_Cost As String
Dim Rng, RngCol As String
Dim Last_Row As Long
Dim i, NewRow As Integer
Set wks = Worksheets("Order Data")
With wks
    Last_Row = .UsedRange.Rows(.UsedRange.Rows.count).Row
    Rng = "A2:D" & Last_Row
    RngCol = "A2:A" & Last_Row
    For i = 2 To Last_Row
        If i = 2 Then

            On Error Resume Next
            PreviousDate = Application.VLookup(CStr(ProdNum), .Range(Rng), 2, False)
            On Error GoTo 0
            If IsError(PreviousDate) Then
                Me.TextBox4.Value = "N/A"
                Me.TextBox5.Value = "N/A"
                Me.TextBox6.Value = "N/A"
                Exit Sub
            End If
            NewRow = Application.Match(CStr(ProdNum), .Range(RngCol), 0) + 2
            Rng = "A" & NewRow & ":D" & Last_Row
            RngCol = "A" & NewRow & ":A" & Last_Row
        ElseIf i > 2 Then
            On Error Resume Next
            Date_Ordered = Application.VLookup(CStr(ProdNum), .Range(Rng), 2, False)
            On Error GoTo 0
            If IsError(Date_Ordered) Then
                NewRow = NewRow - 1
                Rng = "A" & NewRow & ":D" & Last_Row
                Me.TextBox4.Value = CDate(PreviousDate)
                Me.TextBox5.Value = Application.VLookup(CStr(ProdNum), .Range(Rng), 3, False)
                Me.TextBox6.Value = Application.VLookup(CStr(ProdNum), .Range(Rng), 4, False)
                Exit Sub
            End If
            NewRow = Application.Match(CStr(ProdNum), .Range(RngCol), 0) + NewRow
            Rng = "A" & NewRow & ":D" & Last_Row
            RngCol = "A" & NewRow & ":A" & Last_Row
            If Date_Ordered > PreviousDate Then PreviousDate = Date_Ordered
        End If
    Next i
    Me.TextBox4.Value = CDate(PreviousDate)
    Me.TextBox5.Value = Application.VLookup(CStr(ProdNum), .Range(Rng), 3, False)
    Me.TextBox6.Value = Application.VLookup(CStr(ProdNum), .Range(Rng), 4, False)
End With
End Sub

这是打开用户形式的代码部分的行，当我单击调试时，它突出显示了prodescription.show line。

Private Sub CommandButton1_Click()
Dim i, r, c As Integer
Dim wks As Worksheet
Dim cellselect As String

Set wks = Workbooks("Data Direct Orders2.xlsx").Worksheets("Direct Items")
With wks
    If ProdNumberCmbBox.ListIndex = -1 Then
        Unload Me
        ErrorMsg.Show
        End
    Else
        For r = 2 To 84
            cellselect = "A" & r
            If .Range(cellselect).Text = ProdNum Then
                ProDescription.Show

                Unload Me
                End
            End If
        Next r
        If c = 0 Then
            Unload Me
            ErrorMsg.Show
            End
        End If
    End If
End With
End Sub

这是初始化用户形式的子例程：

Private Sub UserForm_Initialize()
TextBox8.Value = ProdNum
Call PastOrderInfo
End Sub

Answer 1

只是弄清楚了。线：

Set wks = Worksheets("Order Data")

在

中

Sub PastOrderInfo()

是问题所在。需要指定工作簿，因此添加以下内容：

Set wks = Workbooks("VBA - Final Project.xlsm").Worksheets("Order Data")

它有效！