Type Mismatch (Error 13) - Looping Through Range And Comparing Cell Value To Textbox Value

Question

I am fairly new to VBA, and I cannot figure out what I am doing wrong or the error. I am trying to loop through a certain range. If a textbox value from a userform is equal to a cell in that range, it inserts a new row and adds input from the userform in that row then ends the for loop. If it is at end of range and still not equal, it adds to row after last row with data.

Dim rng As Range
Set rng = ("F2:F1000")

For Each cell In rng
    If cell.Text = TextBox6.Text Then
        rng.EntireRow.Insert Shift:=xlDown
        ws.Range("A" & rng).Value = TextBox13.Text
        ws.Range("B" & rng).Value = TextBox2.Text
        ws.Range("C" & rng).Value = TextBox3.Text
        ws.Range("D" & rng).Value = TextBox4.Text
        ws.Range("E" & rng).Value = TextBox5.Text
        ws.Range("F" & rng).Value = TextBox6.Text
        ws.Range("G" & rng).Value = TextBox7.Text
        ws.Range("H" & rng).Value = TextBox8.Text
        ws.Range("I" & rng).Value = TextBox9.Text
        ws.Range("J" & rng).Value = TextBox10.Text
        ws.Range("K" & rng).Value = TextBox11.Text
        ws.Range("L" & rng).Value = TextBox12.Text

        Exit For

    ElseIf Cells(1000, "F") And cell.Text <> TextBox6.Text Then
        Dim LastRow As Long, ws As Worksheet
        Set ws = Sheets("Inventory Overview")
        LastRow = ws.Range("A" & Rows.count).End(xlUp).Row + 1 'Finds the last blank row
    '         Inserts Data
            ws.Range("A" & LastRow).Value = TextBox13.Text
            ws.Range("B" & LastRow).Value = TextBox2.Text
            ws.Range("C" & LastRow).Value = TextBox3.Text
            ws.Range("D" & LastRow).Value = TextBox4.Text
            ws.Range("E" & LastRow).Value = TextBox5.Text
            ws.Range("F" & LastRow).Value = TextBox6.Text
            ws.Range("G" & LastRow).Value = TextBox7.Text
            ws.Range("H" & LastRow).Value = TextBox8.Text
            ws.Range("I" & LastRow).Value = TextBox9.Text
            ws.Range("J" & LastRow).Value = TextBox10.Text
            ws.Range("K" & LastRow).Value = TextBox11.Text
            ws.Range("L" & LastRow).Value = TextBox12.Text

    End If

Next cell

Answer 1

You should qualify your ranges with a worksheet so that VBA doesn't assume ActiveSheet, also I'm pretty sure you had to change rng.EntireRow.Insert Shift:=xlDown to cell.EntireRow.Insert Shift:=xlDown so that it inserts a single row I believe the following should do what you expect:

Private Sub CommandButton1_Click()
Dim ws As Worksheet: Set ws = Sheets("Inventory Overview")
'declare and set your worksheet, amend as required
Dim LastRow As Long
Dim bool As Boolean
Dim rng As Range
Set rng = ws.Range("F2:F1000")
bool = False
For Each Cell In rng 'loop to see if you can find Textbox Value in Column F
    If Cell.Text = TextBox6.Text Then
        Cell.EntireRow.Insert Shift:=xlDown
        ws.Range("A" & Cell.Offset(-1, 0).Row).Value = TextBox13.Text
        ws.Range("B" & Cell.Offset(-1, 0).Row).Value = TextBox2.Text
        ws.Range("C" & Cell.Offset(-1, 0).Row).Value = TextBox3.Text
        ws.Range("D" & Cell.Offset(-1, 0).Row).Value = TextBox4.Text
        ws.Range("E" & Cell.Offset(-1, 0).Row).Value = TextBox5.Text
        ws.Range("F" & Cell.Offset(-1, 0).Row).Value = TextBox6.Text
        ws.Range("G" & Cell.Offset(-1, 0).Row).Value = TextBox7.Text
        ws.Range("H" & Cell.Offset(-1, 0).Row).Value = TextBox8.Text
        ws.Range("I" & Cell.Offset(-1, 0).Row).Value = TextBox9.Text
        ws.Range("J" & Cell.Offset(-1, 0).Row).Value = TextBox10.Text
        ws.Range("K" & Cell.Offset(-1, 0).Row).Value = TextBox11.Text
        ws.Range("L" & Cell.Offset(-1, 0).Row).Value = TextBox12.Text
        bool = True 'if found change flag to True
        Exit For
    End If
Next Cell

If bool = False Then 'if not found in the previous loop then add to last row
    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row
        ws.Range("A" & LastRow).Value = TextBox13.Text
        ws.Range("B" & LastRow).Value = TextBox2.Text
        ws.Range("C" & LastRow).Value = TextBox3.Text
        ws.Range("D" & LastRow).Value = TextBox4.Text
        ws.Range("E" & LastRow).Value = TextBox5.Text
        ws.Range("F" & LastRow).Value = TextBox6.Text
        ws.Range("G" & LastRow).Value = TextBox7.Text
        ws.Range("H" & LastRow).Value = TextBox8.Text
        ws.Range("I" & LastRow).Value = TextBox9.Text
        ws.Range("J" & LastRow).Value = TextBox10.Text
        ws.Range("K" & LastRow).Value = TextBox11.Text
        ws.Range("L" & LastRow).Value = TextBox12.Text
End If
End Sub