☰

应用程序和对象定义运行时错误 1004 - 使用 for 循环填充单元格（x，y）

tag-icon • tag-icon microsoft-excel vba macros
应用程序和对象定义运行时错误 1004 - 使用 for 循环填充单元格（x，y）

我正在使用下面的代码尝试找到 Var1 和 Var2 的交点，并将该值填充到我正在运行的用户表单中。

我没有遇到任何找到交点的问题，但每次我尝试设置值时，都会出现 1004 运行时错误。有人知道这里出了什么问题吗？

Private Sub MultiBox_Change()

Dim oSht As Worksheet
Dim lastRow As Long, i As Long
Dim strSearch As String
Dim t As Long
Dim Var2 As String
Dim P As Long
Dim Var1 As String
Dim x As Long


x = 1



On Error GoTo Err
Set oSht = Sheets("Prices")
lastRow = oSht.Range("A" & Rows.Count).End(xlUp).Row
Var1 = FirstBox.Value & " " & SecondBox.Value & " " & ThirdBox.Value & " " & FourBox.Value
Var2 = FifthBox.Value & " " & SixthBox.Value
For i = 1 To lastRow
If oSht.Range("A" & i).Value = Var1 Then
        For P = 2 To 300
            If Cells(x, P) = Var2 Then
            **PredefinedForm.Value = Cells(P, A).Value**
        Exit Sub
    End If
    Next P
    End If
Next i

  Exit Sub
Err:
MsgBox Err.Description


End Sub

多谢你们 ：）

编辑：：

也一直在使用下面的代码来查找 x 与集合 Y 相交的值，并且完全没有任何问题。

Dim oSht As Worksheet
Dim lastRow As Long, i As Long
Dim strSearch As String
Dim t As Long
Dim Var1 As String

 On Error GoTo Err
Set oSht = Sheets("Sheet9")
lastRow = oSht.Range("A" & Rows.Count).End(xlUp).Row
Var1 = FirstBox.Value & " " & SecondBox.Value & " " & ThirdBox.Value & " " & FourBox.Value
For i = 1 To lastRow
    If oSht.Range("A" & i).Value = Device Then
        Predefined.Value = oSht.Range("C" & i)
        Exit Sub
    End If
Next i
Exit Sub
Err:
MsgBox Err.Description


End Sub

答案1

好的，问题已经解决，并且经过测试确认工作正常，问题是我试图根据不存在的变量而不是正在循环的变量来引用行 ID。

为了后代的缘故：确保每次尝试使用变量时，都使用已声明的正确变量。

固定代码供参考：

Private Sub MultiBox_Change()

Dim oSht As Worksheet
Dim lastRow As Long, i As Long
Dim strSearch As String
Dim t As Long
Dim Var2 As String
Dim P As Long
Dim Var1 As String
Dim x As Long


x = 1



On Error GoTo Err
Set oSht = Sheets("Prices")
lastRow = oSht.Range("A" & Rows.Count).End(xlUp).Row
Var1 = FirstBox.Value & " " & SecondBox.Value & " " & ThirdBox.Value & " " & FourBox.Value
Var2 = FifthBox.Value & " " & SixthBox.Value
For i = 1 To lastRow
If oSht.Range("A" & i).Value = Var1 Then
    For P = 2 To 300
        If Cells(x, P) = Var2 Then
        **PredefinedForm.Value = Cells(P, i).Value**
    Exit Sub
End If
Next P
End If
Next i

  Exit Sub
Err:
MsgBox Err.Description


End Sub

相关内容