VBA index match array

[vhdhfox]

Hi,

im trying to use an index match formula in VBA to skip the rows that have already been input in to the userform (NameBox1VF...) but so far the code im using will only populate the text boxes with the first rows data "E2" instead of continuing down the column.

Private Sub UserForm_Initialize()

    Dim A As Variant, B As Variant, C As Variant, D As Variant
    Dim X As Variant
   

    Me.ContainerCMBVF.Text = QueryForm.ContainerBox1.Value
    Me.Textbox2.Text = QueryForm.ClientBox1.Value
    Me.Textbox3.Text = QueryForm.AgencyBox1.Value
    Me.Textbox4.Text = QueryForm.TaskBox1.Value
    Me.Textbox5.Text = QueryForm.DateBox1.Value
   
    A = ContainerCMBVF.Value
    Sheets("Planning").Activate
   
    If A = "" Then
        B = ""
    Let NameBox1VF.Text = B
        C = ""
    Let NameBox2VF.Text = C
        D = ""
    Let NameBox3VF.Text = D
   
        Else
       
    B = Application.WorksheetFunction.Index(Sheets("Planning").Range("E2:E100"), Application.WorksheetFunction.Match(A, Sheets("Planning").Range("F2:F18"), 0))

        Let NameBox1VF.Text = B
   
    C = Application.WorksheetFunction.Index(Sheets("Planning").Range("E2:E100"), Application.WorksheetFunction.Match(A, Sheets("Planning").Range("F2:F18"), 0))

        Let NameBox2VF.Text = C
   
    D = Application.WorksheetFunction.Index(Sheets("Planning").Range("E2:E100"), Application.WorksheetFunction.Match(A, Sheets("Planning").Range("F2:F18"), 0))

        Let NameBox3VF.Text = D
   
    End If
   
           
End Sub

 

[mart37]

I think your second match must start from the row where the first match was found.
I haven't test the code.

StartRow=2
SearchRow = Application.WorksheetFunction.Match(A, Sheets("Planning").Range("F" & StartRow & ":F18"), 0)
B = Application.WorksheetFunction.Index(Sheets("Planning").Range("E" & StartRow & ":E100"), SearchRow)
Let NameBox1VF.Text = B
StartRow = StartRow + SearchRow -1
SearchRow = Application.WorksheetFunction.Match(A, Sheets("Planning").Range("F" & StartRow & ":F18"), 0)
C = Application.WorksheetFunction.Index(Sheets("Planning").Range("E" & StartRow & ":E100"), SearchRow)
Let NameBox2VF.Text = C
StartRow = StartRow + SearchRow -1
SearchRow = Application.WorksheetFunction.Match(A, Sheets("Planning").Range("F" & StartRow & ":F18"), 0)
D = Application.WorksheetFunction.Index(Sheets("Planning").Range("E" & StartRow & ":E100"), SearchRow)
Let NameBox3VF.Text = D

 

[vhdhfox]

I think your second match must start from the row where the first match was found.
I haven't test the code.

StartRow=2
SearchRow = Application.WorksheetFunction.Match(A, Sheets("Planning").Range("F" & StartRow & ":F18"), 0)
B = Application.WorksheetFunction.Index(Sheets("Planning").Range("E" & StartRow & ":E100"), SearchRow)
Let NameBox1VF.Text = B
StartRow = StartRow + SearchRow -1
SearchRow = Application.WorksheetFunction.Match(A, Sheets("Planning").Range("F" & StartRow & ":F18"), 0)
C = Application.WorksheetFunction.Index(Sheets("Planning").Range("E" & StartRow & ":E100"), SearchRow)
Let NameBox2VF.Text = C
StartRow = StartRow + SearchRow -1
SearchRow = Application.WorksheetFunction.Match(A, Sheets("Planning").Range("F" & StartRow & ":F18"), 0)
D = Application.WorksheetFunction.Index(Sheets("Planning").Range("E" & StartRow & ":E100"), SearchRow)
Let NameBox3VF.Text = D

thank you so much that works great, I removed the (-1) as it was returning the same value and it works perfectly displaying the next data.