Copying data from adjacent cell based on combobox value

[kjgallier]

I have a spreadsheet called Eque2_Contracts that contains various columns of data. The first column A contains multiple codes, for example 00120, 00130 etc from A2 downwards. This can be added to with new records at any time.

I also have a combobox1 that lists all individual codes from this first column. I also then have a listbox1.

When combobox1 value is selected, I want the script to check A2, A3, A4 etc in my spreadsheet against that combobox1 value. If it finds the combobox1 value in cell A2, A3, A4 I want it to list cell B2 data into listbox1. (The adjacent info)

BUT I need it to search ALL of column A, as the codes can appear more than once. Each time it finds the combobox1 value in column A, I need the data in the adjacent B cell added to the listbox.

Each time a new combobox1.value is selected, it needs to clear the listbox1, search again as above for the occurrences of that code and copy the adjacent data in column B into the listbox.

I got this far (thinking I should create a library containing both columns of data for faster searching) now unsure how to check that data?

Dim Cl As Range
Dim Dic As Object

Set Dic = CreateObject("scripting.dictionary")
With Sheets("Eque2_Contracts")
For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
Dic(Cl.Value) = Array(Cl, Cl.Offset(, 1).Value)
Next Cl
End With

Any help would be much appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub ComboBox1_Click()
   Dim Ary As Variant
   
   With Sheets("Eque2_Contracts")
      With .Range("A2:B" & .Range("A" & Rows.Count).End(xlUp).Row)
         Ary = Filter(.Worksheet.Evaluate("transpose(if(" & .Columns(1).Address & "=""" & Me.ComboBox1.Value & """," & .Columns(2).Address & ",char(2)))"), Chr(2), False)
      End With
   End With
   Me.ListBox1.List = Ary
      
End Sub

 

[kjgallier]

Hi & welcome to MrExcel.
How about

Private Sub ComboBox1_Click()
   Dim Ary As Variant
  
   With Sheets("Eque2_Contracts")
      With .Range("A2:B" & .Range("A" & Rows.Count).End(xlUp).Row)
         Ary = Filter(.Worksheet.Evaluate("transpose(if(" & .Columns(1).Address & "=""" & Me.ComboBox1.Value & """," & .Columns(2).Address & ",char(2)))"), Chr(2), False)
      End With
   End With
   Me.ListBox1.List = Ary
     
End Sub

Thank you for the swift reply. I will test this later. Incidentally what is the char in your code? Obviously character but related to?
Do I need to add a clear listbox statement at the beginning for each time a new combobox value is selected. Your assistance is much appreciated. If you could talk me through what the code is doing?

 

[Fluff]

what is the char in your code?

It's just a character that will not appear in your data.

Do I need to add a clear listbox

Nope, that will happen automatically.

The code is basically a normal xl If function

=IF(A2:A10="00120",B2:B10,Char(2))

and then the VBA Filter function is used to remove all entries that are Char(2) so you just get the values of interest remaining.

 

[kjgallier]

It's just a character that will not appear in your data.

Nope, that will happen automatically.

The code is basically a normal xl If function

=IF(A2:A10="00120",B2:B10,Char(2))

and then the VBA Filter function is used to remove all entries that are Char(2) so you just get the values of interest remaining.

This worked perfectly. I have analysed the code to understand it, but thank you. I used to code in Delphi many years ago. This is a little project of mine that no doubt I will need further help with! But I will try to go alone ha ha.

 

[kjgallier]

This worked perfectly. I have analysed the code to understand it, but thank you. I used to code in Delphi many years ago. This is a little project of mine that no doubt I will need further help with! But I will try to go alone ha ha.

It's just a character that will not appear in your data.

Nope, that will happen automatically.

The code is basically a normal xl If function

=IF(A2:A10="00120",B2:B10,Char(2))

and then the VBA Filter function is used to remove all entries that are Char(2) so you just get the values of interest remaining.

Can I ask a further question?
Your code pulls in the data from the adjacent cell. Populates listbox. GREAT!

I now want to pull in data from other columns that are along the same row in the spreadsheet as the selected info/line in my listbox. I am going to use that data to populate a few different text boxes on the same form.

So for example, if I select a row in the listbox, it will find the data from column G for example (same row as listbox) and pop value in a text box. Column H same row will appear in another text box on the same form.

ANY help would be much appreciated.

 

[Fluff]

As this is a totally different question, it needs a new thread. Thanks