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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.