Error in nested For Next Statement

[blounty]

Hi Guys,

I have the following code which is Type Mismatch erroring.

Private Sub CommandButton1_Click()
Dim I As Long
Dim Src As String
Dim OWG As String
Dim NWG As String
Dim NMK As String
Dim OMake As String
Dim NMake As String
Dim OModel As String
Dim NModel As String
Dim Rental As String


If UserForm6.ComboBox1.Value = "" Then

MsgBox "Please Choose A Device Entry Number"

Else

If UserForm6.ComboBox2.Value = "" Then

MsgBox "Please Enter A New Device Referece"

Else

If UserForm6.ComboBox3.Value = "New" Then

With Worksheets("Proposal")
    For I = 4 To 500
        Src = "C" & I
        OWG = "J" & I
        NWG = "B" & I
        NEWMOVEKEEP = "D" & I
        OMake = "E" & I
        NMake = "E" & I
        OModel = "F" & I
        NModel = "F" & I
        Rental = "G" & I
        OMCPP = "M" & I
        NMCPP = "I" & I
        OCCPP = "N" & I
        NCCPP = "K" & I
        NDR = "C" & I
      
        
        
        If .Range(Src) = CInt(UserForm6.ComboBox1.Value) Then
        
        Worksheets("Workgroup_Breakdown").Range(NDR).Value = UserForm6.ComboBox2.Value
        Worksheets("Workgroup_Breakdown").Range(NWG).Value = Worksheets("Proposal").Range(OWG).Value
        Worksheets("Workgroup_Breakdown").Range(NEWMOVEKEEP).Value = UserForm6.ComboBox3.Value
        Worksheets("Workgroup_Breakdown").Range(NMake).Value = UserForm6.TextBox2.Value
        Worksheets("Workgroup_Breakdown").Range(NModel).Value = UserForm6.TextBox3.Value
        Worksheets("Workgroup_Breakdown").Range(Rental).Value = UserForm6.TextBox4.Value
        Worksheets("Workgroup_Breakdown").Range(NMCPP).Value = UserForm6.TextBox5.Value
        Worksheets("Workgroup_Breakdown").Range(NCCPP).Value = UserForm6.TextBox6.Value
        
        UserForm6.Frame1.Visible = False
        
        End If
    Next I
    
End With

        UserForm6.ComboBox1.Text = ""
        UserForm6.ComboBox2.Text = ""
        UserForm6.TextBox1.Text = ""
        UserForm6.TextBox2.Text = ""
        UserForm6.TextBox3.Text = ""
        UserForm6.TextBox4.Text = ""
        UserForm6.TextBox5.Text = ""
        UserForm6.TextBox6.Text = ""
Else
        If UserForm6.ComboBox3.Value = "Move" Then
With Worksheets("Proposal")
    For I = 4 To 500
        Src = "C" & I
        OWG = "J" & I
        NWG = "B" & I
        NEWMOVEKEEP = "D" & I
        OMake = "E" & I
        NMake = "E" & I
        OModel = "F" & I
        NModel = "F" & I
        Rental = "G" & I
        OMCPP = "M" & I
        NMCPP = "I" & I
        OCCPP = "N" & I
        NCCPP = "K" & I
        NDR = "C" & I
      
        
        
        If .Range(Src) = CInt(UserForm6.ComboBox1.Value) Then
        
        Worksheets("Workgroup_Breakdown").Range(NDR).Value = UserForm6.ComboBox2.Value
        Worksheets("Workgroup_Breakdown").Range(NWG).Value = Worksheets("Proposal").Range(OWG).Value
        Worksheets("Workgroup_Breakdown").Range(NEWMOVEKEEP).Value = UserForm6.ComboBox3.Value
        Worksheets("Workgroup_Breakdown").Range(NMCPP).Value = UserForm6.TextBox5.Value
        Worksheets("Workgroup_Breakdown").Range(NCCPP).Value = UserForm6.TextBox6.Value
        
End If
Next I
End With

With Worksheets("Workgroup_Breakdown")
    For I = 4 To 500
    For j = 4 To 500
        Src = "C" & I
        PMake = "E" & I
        PModel = "F" & I
        Entry = "A" & j
        CMake = "E" & I
        CModel = "F" & I
        
        [b]If .Range(Src) = CInt(UserForm6.ComboBox2.Value) Then[/b]
        If .Range(Entry) = CInt(UserForm6.ComboBox1.Value) Then
        
        Worksheets("Workgroup_Breakdown").Range(PMake).Value = Worksheets("Workgroup_Breakdown").Range(CMake).Value
       
        End If
        End If
        Next j
        Next I
        End With



        UserForm6.ComboBox1.Text = ""
        UserForm6.ComboBox2.Text = ""
        UserForm6.TextBox1.Text = ""
        UserForm6.TextBox2.Text = ""
        UserForm6.TextBox3.Text = ""
        UserForm6.TextBox4.Text = ""
        UserForm6.TextBox5.Text = ""
        UserForm6.TextBox6.Text = ""

        


Else
If UserForm6.ComboBox3.Value = "Existing" Then
With Worksheets("Proposal")
    For I = 4 To 500
        Src = "C" & I
        OWG = "J" & I
        NWG = "B" & I
        NEWMOVEKEEP = "D" & I
        OMake = "E" & I
        NMake = "E" & I
        OModel = "F" & I
        NModel = "F" & I
        Rental = "G" & I
        OMCPP = "M" & I
        NMCPP = "I" & I
        OCCPP = "N" & I
        NCCPP = "K" & I
        NDR = "C" & I
      
        
        
        If .Range(Src) = CInt(UserForm6.ComboBox1.Value) Then
        
        Worksheets("Workgroup_Breakdown").Range(NDR).Value = UserForm6.ComboBox2.Value
        Worksheets("Workgroup_Breakdown").Range(NWG).Value = Worksheets("Proposal").Range(OWG).Value
        Worksheets("Workgroup_Breakdown").Range(NEWMOVEKEEP).Value = UserForm6.ComboBox3.Value
        Worksheets("Workgroup_Breakdown").Range(NMCPP).Value = UserForm6.TextBox5.Value
        Worksheets("Workgroup_Breakdown").Range(NCCPP).Value = UserForm6.TextBox6.Value
        
End If
Next I
End With
        UserForm6.ComboBox1.Text = ""
        UserForm6.ComboBox2.Text = ""
        UserForm6.TextBox1.Text = ""
        UserForm6.TextBox2.Text = ""
        UserForm6.TextBox3.Text = ""
        UserForm6.TextBox4.Text = ""
        UserForm6.TextBox5.Text = ""
        UserForm6.TextBox6.Text = ""

End If
End If
End If
End If
End If
Sheets("Workgroup_Breakdown").Select

ListBox2.RowSource = "a4:L500"

It is erroring on the bold line.

I have been looking at this for ages now and am so confused. could anyone help me....

Thanks Guys

 

[TonyJollans]

The error is probably caused by UserForm6.ComboBox2.Value having a value which cannot be converted to an integer. I haven't checked out all your code but you don't appear to be validating it beyond checking for it being empty.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association