Extract a dynamic list from a range in Excel based on specific criteria

[Raghav Chamadiya]

I want to extract serial numbers(Column A) from a range of data based on the value of column P (Release status). So, if the status is pending I want to add the serial number in my list.
In the end I want to show this dynamic list in a listbox in userform, where he will be able to further interact with it. Hence I want this done through VBA instead of formulae.
Thanks a lot

 

[Fluff]

Hi & welcome to MrExcel.
Will you just want the serial number in the listbox?

 

[Raghav Chamadiya]

Hi & welcome to MrExcel.
Will you just want the serial number in the listbox?

@Fluff yes

 

[Fluff]

In that case, how about

Private Sub UserForm_Initialize()
   Dim Rng As Range
   Dim Ary As Variant
   
   With Sheets("Sheet1")
      Set Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
   End With
   Ary = Evaluate("transpose(if(" & Rng.Offset(, 15).Address(, , , 1) & "=""Pending""," & Rng.Resize(, 3).Address(, , , 1) & ",false))")
   Me.ListBox1.List = Filter(Ary, False, False)
End Sub

 

[Raghav Chamadiya]

I just made a very small change because I wanted to look up the data from a different file:

Private Sub UserForm_Initialize()
   Dim Rng As Range
   Dim Ary As Variant
   
   Dim nwb As Workbook
   Set nwb = Workbooks.Open("C:\Users\CHAMARA2\Downloads\Planning Sheet\Database.xlsm")
   
   With nwb.Sheets("Sheet1")
      Set Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
   End With
   Ary = Evaluate("transpose(if(" & Rng.Offset(, 15).Address(, , , 1) & "=""Pending""," & Rng.Resize(, 3).Address(, , , 1) & ",false))")
   Me.ListBox1.List = Filter(Ary, False, False)
End Sub

The problem is when I launch the form it gives me a type mismatch error and just takes me to the form without exactly telling where the problem is.

 

[Fluff]

Not sure what happened there, but it should be

Ary = Evaluate("transpose(if(" & Rng.Offset(, 15).Address(, , , 1) & "=""Pending""," & Rng.Address(, , , 1) & ",false))")

 

[rlv01]

Set Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))

@Fluff - Was there a reason to leave Rows.Count unqualified, or is it just a typo?

 

[Raghav Chamadiya]

@Fluff works perfectly now. Thank you so much. ??

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Fluff]

Was there a reason to leave Rows.Count unqualified, or is it just a typo?

Unless your dealing with different workbooks there is no need to qualify it. And even then you only need to qualify it if one of the books is an "old style" xls file.