2

I'm using the below code to try and find the intersect of Var1 and Var2, and populate the value into my userform that I'm running.

I have no problem finding the intersect, but every time I try and set the value, I get that 1004 runtime error. Does anyone know what's going wrong here at all?

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

Thanks guys :)

EDIT::

Have also been using the below code for finding value of x intersect with set Y, and has no issues at all.

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
2
  • 1
    I don't see where you're declaring and assigning A in Cells(P, A).Value
    – Sathyajith Bhat
    Dec 23, 2014 at 9:18
  • Thanks @Sathya have defined A as long, and still having the same error. Have also tried remove definition for P. Dec 24, 2014 at 0:43

1 Answer 1

1

Okay, have fixed issue, and tested to confirm working Issue was I attempted to reference the row ID based on a non-existant variable, not the variable which was being looped.

For posterity sake: Make sure every time trying to use a variable, you are using the right variable already declared.

Fixed code for reference:

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .