UserForm ListBox with 2D Array and Match

[SeanMathew]

Relatively new with VBA and doing my best to learn it. I have a userform which has a listbox. To this I would like to add some type of Match function so that the listbox only displays the rows in which a certain value is displayed. For example, the project ID is displayed in Sheet1 cell B1. In the table, the corresponding project IDs are in Sheet2 column A. I would like the listbox to only show the rows in which the value in Sheet 2 Column A match the value in Sheet 1 cell B1.

Currently the listbox displays all of the rows from the sourced table with only select columns. How can I modify this to include the Match function as described above. Open to other methods as well (i.e. filter, etc).

Current Code:

Sub MRReset

With MRForm
.ComboBox1.Value = ""
.ComboBox2.Value = ""
.ComboBox3.Value = ""
.ComboBox4.Value = ""
.TextBox1.Value = ""
.ComboBox6.Value = ""
.ComboBox7.Value = ""
.TextBox2.Value = ""
.ComboBox9.Value = ""
.ComboBox10.Value = ""
.TextBox3.Value = ""
.MRRowNumber.Value = ""
End With

sn = Sheets("DataTable").Range("A1:O1000").Value
sp = Application.Index(sn, [Row(1:999)], Array(1, 7, 15))
'Referenced from a third party with original source: VBA for smarties: Arrays, Title 6.7.1

End Sub

 

[Fluff]

You don't need to mention the cross post in every thread, just when you start the thread.

Because the listbox is getting filtered you will need to loop through the data to find the correct record.

 

[SeanMathew]

With regards to the code provided for the listbox, can you offer a little explanation. I'm learning all of this through online resources so I'm not familiar with the expressions being used. I defined the Chr(34) through ASCII but what are the @ and # expressions? More importantly, how are they being defined?

My issue with looping through the data to find the correct record is that there may not be a distinguishing variable. Multiple rows can have the same variables within a given project ID. Can the loop be set to find the correct record based on the row number?

 

[Fluff]

Would you be willing to have an extra column that contains the row number?

 

[SeanMathew]

Would you be willing to have an extra column that contains the row number?

I think it would be a reasonable compromise so certainly not opposed to it.

 

[Fluff]

Ok I have added the formula

=ROW()

in P2 copied down & then in the Reset code use

   Id = Sheets("controls").Range("B1")
   With Sheets("Datatable").Range("A2:P1000")
      Rws = Filter(.Worksheet.Evaluate(Replace(Replace("transpose(if(@=" & Chr(34) & Id & Chr(34) & ",row(@)-#,false))", "@", .Columns(1).Address), "#", .Rows(0).Row)), False, False)
      ReDim Preserve Rws(UBound(Rws) + 1)
      Ary = Application.Index(.Value, Application.Transpose(Rws), Array(1, 7, 15, 16))
   End With

and n the edit button click use

With Me.ListBox1
   If .ListIndex = -1 Then
       MsgBox "No row is selected.", vbOKOnly + vbInformation, "Edit"
       Exit Sub
   End If
   Me.MRRowNumber.Value = .List(.ListIndex, 3)
End With

'Code to update the values to respective controls
Dim sht1 As Worksheet

 

[SeanMathew]

Works perfectly, thank you!

Can you offer a brief explanation of the @ and # expressions, or a good source to review for this?

 

[Fluff]

Both the @ & # signs are just place holders & get replaced by the bits at the end of the code, so you end up with a formula like

if(.Columns(1).Address=" & Chr(34) & Id & Chr(34) & ",row(.Columns(1).Address)-.Rows(0).Row,false)

which roughly equates to

if(A2=Id,row(A2)-1,false)

although the code looks at every row in "one hit" rather than looping through them.

 

[SeanMathew]

Both the @ & # signs are just place holders & get replaced by the bits at the end of the code, so you end up with a formula like

if(.Columns(1).Address=" & Chr(34) & Id & Chr(34) & ",row(.Columns(1).Address)-.Rows(0).Row,false)

which roughly equates to

if(A2=Id,row(A2)-1,false)

although the code looks at every row in "one hit" rather than looping through them.

Thank you Fluff! I really appreciate all of your help solving this issue.

I was holding off on responding because I was trying to implement this code on another form in the same workbook. Initially I had an issue but I figured it out (there were some different references that needed to be changed).

 

[Fluff]

You're welcome & thanks for the feedback.