Code to Add Data From UserForm In Row Above or Below Based on Criteria

[amoverton2]

Hi!

I'm concerned with this part as I can't get it to run the way I want to. I need a fresh set of eyes!

Basic information:
TextBox24 = A number: 1, 2, 3, 4, etc.
Column I is where TextBox24 is to land
Data currently in Row 1: Column H = 252525, Column F = AAAAA Column I = -
Row 2: Column H = 252525, Column F = BBBBB Column I = 1
Row 3: Column H = 252525, Column F = BBBBB Column I = 4
Row 4: Column H = 252525, Column F = BBBBB Column I = 5
Row 5: Column H = 252525, Column F = BBBBB Column I = 7
*Note: there is other data in those rows but I'm concerned with only those three columns

So the plain language explanation is this: when clicking the button I want the incoming data (after targeting the sheet from TextBox1) to check the last row to match Column H then Column F, then Column I. At Column I, I want the code to make a determination if the number in TextBox24 (from the userform) is either greater than or less then the number in the last row. If greater than place the data on the next row below (i.e. TextBox24 = 8 then it would goto row 6). If less than, I want it to check the next row above in Column I to see if it is less than that number, if it is less than that then goto the next row above until it finds a value in Column I that it is greater than, then insert a row below the number it is greater than and place all the data from the userform on that row. For example: If TextBox24 = 2, it would check against row 5 (7), then move to up row 4 (5) then move up to row 3 (4), then at row 2 (1) since it is greater, insert row (shifting all rows down) and now TextBox24 = 2 is on row 3. So, at the end of the day if Columns H and F match, Column I should be sequential from lowest to highest number.

This is part of the code where that magic is supposed to happen but I can't get it to work. (If the whole code needs a rewrite then so be it, I appreciate the help immensely).

ElseIf Me.TextBox3.Value = "BBBBBB" Then
        newValue = CDbl(Me.TextBox24.Value)
        destRow = lastRow
       
        If newValue < ws.Cells(lastRow, "I").Value Then
            For i = lastRow To 2 Step -1
                If ws.Cells(i, "H").Value = Me.TextBox2.Value And ws.Cells(i, "F").Value = "BBBBBB" Then
                    If newValue < ws.Cells(i, "I").Value Then
                       If i = 2 Or ws.Cells(i - 1, "I").Value < newValue Then
                            destRow = i
                            Exit For
                        End If
                    End If
                End If
            Next i
        End If
       
        If newValue >= ws.Cells(lastRow, "I").Value Then
            destRow = lastRow + 1
        End If
       
        Set newRow = tbl.ListRows.Add(destRow)

Here is the whole code for reference:

Private Sub cmbXFER_Click()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim newRow As ListRow
    Dim lastRow As Long
    Dim destRow As Long
    Dim i As Long
    Dim newValue As Variant

    Set ws = ThisWorkbook.Sheets(Me.TextBox1.Value)
    Set tbl = ws.ListObjects(1)

    lastRow = tbl.ListRows.Count + 1

    If lastRow = 1 And ws.Cells(1, "A").Value = "" Then lastRow = 1
  
    If Me.TextBox3.Value = "AAAAA" Then
        Set newRow = tbl.ListRows.Add
    ElseIf Me.TextBox3.Value = "BBBBBB" Then
        newValue = CDbl(Me.TextBox24.Value)
       
        destRow = lastRow
   
       If newValue < ws.Cells(lastRow, "I").Value Then
            For i = lastRow To 2 Step -1
                If ws.Cells(i, "H").Value = Me.TextBox2.Value And ws.Cells(i, "F").Value = "BBBBBB" Then
                    If newValue < ws.Cells(i, "I").Value Then
                         If i = 2 Or ws.Cells(i - 1, "I").Value < newValue Then
                           destRow = i
                            Exit For
                        End If
                    End If
                End If
            Next i
        End If
       
        If newValue >= ws.Cells(lastRow, "I").Value Then
            destRow = lastRow + 1
        End If
       
        Set newRow = tbl.ListRows.Add(destRow)
    ElseIf Me.TextBox3.Value = "CCCCCC" Or Me.TextBox3.Value = "DDDDD" Then
        destRow = lastRow
        For i = lastRow To 2 Step -1
            If ws.Cells(i, "H").Value = Me.TextBox2.Value Then
                destRow = i + 1
                Exit For
            End If
        Next i
       Set newRow = tbl.ListRows.Add(destRow)
    End If

    With newRow
        .Range(1).Value = Me.TextBox1.Value
        .Range(2).Value = Me.cmbXYXYXY.Value
        .Range(3).Value = Me.cmbABABAB.Value
        .Range(4).Value = Me.cmbMNMNMN.Value
        .Range(5).Value = Me.cmbPQPQPQ.Value
        .Range(6).Value = Me.TextBox3.Value
        .Range(7).Value = Me.TextBox14.Value
        .Range(8).Value = Me.TextBox2.Value
        .Range(9).Value = Me.TextBox24.Value
        .Range(10).Value = Me.TextBox12.Value
        .Range(11).Value = Me.TextBox13.Value
        .Range(12).Value = Me.TextBox4.Value
        .Range(13).Value = Me.TextBox9.Value
        .Range(14).Value = Me.TextBox7.Value
        .Range(15).Value = Me.TextBox10.Value
        .Range(16).Value = Me.TextBox20.Value
        .Range(17).Value = Me.TextBox21.Value
        .Range(18).Value = Me.TextBox22.Value
        .Range(19).Value = Me.TextBox11.Value
        .Range(20).Value = Me.TextBox15.Value
        .Range(21).Value = Me.TextBox16.Value
        .Range(22).Value = Me.TextBox17.Value
    End With
End Sub

Thank you
Adam

 

[amoverton2]

Please see attached link for the file

Test Book.xlsb

I've played around with the code since I first posted, However, I'm still having trouble with scenario's 2 and 3 to get data to insert in the correct place, if data is not inputted via the userform in sequential order.
For example, UNIT: BENNYS, Order Number: 11111, Order Type: Update, Order Update Number: 3 (and the rest of the info from the userform) should be inserted below row 4 to make that Order Number 11111 sequentially.
The rest of the sheets have similar problems to run against.