Add new data entry in a new row under existing row in middle of table

Linesol 

Thanks for the kind words.

Before trying to solve the problem, let's learn about tables in VBA. In VBA they are ListObjects.

Dim oLo as ListObject

Getting (Setting) ListObjects

ListObjects belong to the worksheet's ListObjects collection. We can select the table from the worksheet's ListObjects collection in several ways. Here are 3.

Set oLo = ThisWorkbook.Sheets("Sheet1").ListObjects(1)

' or

Set oLo = Sheet1.ListObjects(1)

' or

Set oLo = Sheet1.ListObjects("Table1")

Note that because a ListObject is an object we must use Set.

We can also select a table by through a range's ListObject property. Any cell occupied by the table will return the ListObject like so (Assume A5 is in the table):

Set oLo = Sheet1.Range("A5").ListObject 

Now that we know ranges occupied by ListObjects can return the ListObject, we can leverage my preferred method: getting a ListObject by Table name. 

Note that table names MUST be unique within a workbook. Excel enforces this rule by tracking table names in its names collection, but the name does not refer to the ListObject, It refers to the table's range. Another odd note is the table name is not accessible from the workbook's names collection directly. We must use VBA's EVALUATE method to evaluate the name and return its range.

Set oLo = EVALUATE("Table1").ListObject 

So in the above, we evaluated the name "Table1" which returned the range that Table1 occupies and that range has a ListObject property that returns the ListObject in those cells.  Lastly, there is an abbreviation for EVALUATE which is [ ]. This is how I get ListObjects:

Set oLo = [Table1].ListObject

ListObject Properties

One of the wonderful things about tables is they expand. That means Excel must keep track of how many rows and columns a ListObject has.

Dim lRow as Long
Dim lCol as Long

lRow = oLo.ListRows.Count 
lCol = oLo.ListColumns.Count

Thus, we can loop through all table rows like so

For lRow = 1 to oLo.ListRows.Count 
...
Next 

And because the ListObject's ListRows is a collection of ListRow objects, we can loop through it using VBA's FOR/EACH:

Dim oLr as ListRow

For Each oLr in oLo.ListRows
...
Next

Each ListRow has an Index (row #) and a collection of cells. Thus we can set the cells in your table like so:

If Not IsEmpty(oLr.Range(2) Then Set oLr = oLo.ListRows.Add(oLr.Index, TRUE)
oLr.Range(2).Value = Me.TextBox2.Value
oLr.Range(3).Value = Me.TextBox3.Value

Okay, that should give you some background on ListObjects and a bit on how to more efficiently address cells and ranges. Now let's go back to your problem. Let's begin with what we learned last time and build on it.

Now I'm not entirely sure I understand the problem. My assumption is we want to

• select cells matching a material number
• if the selection's column 2 is not blank, add a row
• Add data in columns 2 & 3 from the form

The code below does this. I've added comments to help explain what each section does. Let me add a bit more explanation below

Private Sub CommandButton1_Click()

'   Declare Variable
    Dim oSelection As Range
    Dim oArea As Range
    Dim oRow As Range
    Dim lRow As Long
    Dim lLastRow As Long
    Dim oLo As ListObject

'   Initialize Variables
    lLastRow = 0
    Set oLo = [Table1].ListObject

'   Select rows
    Application.ScreenUpdating = False
    [Table1].AutoFilter Field:=1, Criteria1:=Me.TextBox1.Value
    Set oSelection = [Table1].SpecialCells(xlCellTypeVisible)
    [Table1].AutoFilter Field:=1
    Application.ScreenUpdating = True

'   Process each area in selection and each row in each area
    For Each oArea In oSelection.Areas
        For Each oRow In oArea.Rows
        '   Calculate table row #
            lRow = oRow.Row - oLo.HeaderRowRange.Row
        '   Process only if we haven't already
            If lLastRow <> lRow Then
            '   If empty, add a new row
                If Not IsEmpty(oRow.Cells(2)) Then
                    lRow = oLo.ListRows.Add(lRow + 1, True).Index
                    Set oRow = oLo.ListRows(lRow).Range
                    oRow.Cells(1) = Me.TextBox1.Value
                End If
            '   Add data from form
                oRow.Cells(2) = Me.TextBox2.Value
                oRow.Cells(3) = Me.TextBox3.Value
                DoEvents
            '   Remember what we just processed
                lLastRow = lRow
            End If
        Next
    Next

    UserForm1.Hide

End Sub

oArea and oRow

When we select filtered ranges there is a good chance the selection will not be contiguous. If that happens there will be multiple areas in the selection and each area could have multiple rows. Whether the selection is contiguous or not, looping through selections by areas and then by rows is always the safe approach.

lLastRow

When we add rows, we change the shape of our selection so the next iteration through the loop will bring up the row we just added and if we process that row we will create a never ending loop that processes new row after new row until we reach Excel's row limit or use up all our memory. So we must remember which row we last processed, and if it is the same row, due to adding a row, skip it.

Hope that helps.