VBA index match array

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
36
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,096
Office Version
  1. 2016
Platform
  1. Windows
I think your second match must start from the row where the first match was found.
I haven't test the code.

VBA 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

New Member
Joined
Aug 6, 2020
Messages
36
Office Version
  1. 2013
Platform
  1. Windows
I think your second match must start from the row where the first match was found.
I haven't test the code.

VBA 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.
 

Forum statistics

Threads
1,176,091
Messages
5,901,363
Members
434,887
Latest member
zoath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top