Search Values in a Column and List them in a ListBox

Hi all,

I'm afraid I've run into some trouble with the Listbox function, which I really love, but I can't quite get past this hurdle.

I want to have a listbox which populates by checking an entire column for values, ignoring blank cells, and, if value is present, to also check to see if any value is present in the cell one column to the right and one row down from the cell that has the initial value. If both these conditions are true, to then display both values in the Listbox. In other words, the Listbox would contain two columns.

During this routine, when checking value is present in the second cell, I'm guessing that an IF statement would increment a variable signifying the cell reference by + 1 for column and + 1 for the one row down?

If anyone can help I'd be really grateful. Thanks.

Hi, Try this:-

Please Login or Register  to view this content.

Regards Mick

Thanks very much Mick. I'm trying to modify the code slightly now to fit another final condition.
Now I'm trying to create a listbox that searches the active sheet and lists each row provided that the row contains any number in column 6. If it does contain a number, to display that entire row. I can't seem to get column headings to work either, which are on the worksheet in cells A5 to K5, and would like those 11 column headers to be present in the single select listbox.

Any ideas?

Hi, Try this:-

Please Login or Register  to view this content.

Regards Mick

Thanks Mick it does work but it returns all rows instead of particular ones. And reading above I don't think I was clear the column headers didn't work either and I'm not sure I can have them for over 10 columns.

So to be clear, it's searching column F from F6 to the end row.
If during this search a value is found in a cell, if a value is not displayed in the next cell to the right of it (i.e. value found in F8 so check to see if a value lies in G8), then list this whole row, which will be 11 columns long.
Else, skip to next cel down in column F until end row.

Sorry if I was unclear above. But if you could help that would be fab.

Hi Mick, thanks for the update. It does work, but it does the reverse of what I want it to.

Instead of only listing rows where there is a value in column F but not G in that row, it instead avoids such a row and lists all rows where there is a value in both F and G.

Hi, Try changing the line:-

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

Mick

Works nicely so thanks very much Mike.

With columns exceeding 10 in a listbox. Do listbox headers work? I can't get them to.

I modified your code slightly to include...

With POUserForm.POListBox
.ColumnHeads = True
.RowSource = A5
.ColumnCount = 11
.ColumnWidths = "40,40,40,40,40,40,40,40,40,40,40"
.List = Ray
End With


...so that the headers would appear. I can see that the headers are initiated when I run the sub but they appear just blank with nothing inside. I thought I had to set the rowsource as the row underneath where the headers are. But something's not going right and it's not picking these up.

Hi, To include the Headers in row (5), alter the "Set Rng" bit of code to start at "A5" then modify the code line similar to below, to reflect the added bit of code.
I think the columns header property only works if you insert the range using the "Listfill rnage" Property, then when you specify the range under the header rows, Excel automicalkly insert the headers.

Please Login or Register  to view this content.

Regards Mick

Hi, Use this bit of code in your TextBox. I see you have not change the First row to Row (5) in the "Set Rng " Statement or added the Statement as per my previous thread "If Dn.row = 5". If you do that you will get your heades in row (5). NB:- The code below is based on The first row being row (5), so if you don't use it you will get you result on the wrong row and will need to alter the code "(Range("G" & 5 + n).Value = TextBox1.Value)" to reflect the correct row.

Please Login or Register  to view this content.

Regards Mick

Hey Mick, thanks. Don't worry I hadn't forgotten the code to make the column headers appear The text box value is now being input into the cell in column G in real time, but it doesn't correspond to the correct row, I think because in the code it is offset by 5 + the value of the listbox, if I am correct. So it's being placed to high up in the column against another row.

This was the big dilemna I was having - how do I get the text box value to be placed in the row the user selected in the listbox so that it is input in that row?

Hi, It depends where you "Rng" starts,Usually the first line in a ListBox is "0" so when you select line 4, the code will read it as line 3, The easiest way is just to add or subtract "1" from the "5" in that line of code & see what the result is, then depending on the result, adjust the "5" accordingly.
Regards Mick

Oh I see. But remember the rows appearing in the listbox are filtered using your code at the beginning of this thread. So basically because only conditional rows are displated, the listbox value doens't mean anything. For example row 3 and 10 from the worksheet might be listed in the listbox, but to return a value from the textbox into the selected listbox row, it would not correspond to the actual row number. Sorry I should've reminded you about that! I just can't find a way!

Sorry, I'd forgotten that.
What you could do is, if the data in the selected row is Unique, then you could search throught the sheet data to find the matching row then input the data, accordingly from the TextBox.
Does that sound possible.
Mick

Definitely sounds like it would work as the row data will always be unique, provided column E in the worksheet is checked against what row is listed and selected in column E of the listbox. But how do I get Excel to 'know' what to search for. It might seem silly, but Excel doesn't exactly know what it's put in the listbox. So when you select, you've got to tell it what you selected even though it searched and filtered results to list in the box in the first place.

I'll recap on the code so far, still without headers:

Please Login or Register  to view this content.

Hi, Try this:-
This code finds the row you've selected in the ListBox, Creates a string of all the values in that ListBox row and Places it in s string variable "Rw", then the code loops through each line in the sheet Concatinating all 12 columns of each row into a string and checking them against the variable "Rw". If it finds a match it places the Text in TextBox1 in column "G" of that row.
This code should cater for any dates you have in your sheet.
NB:- It would be better to run this code from a Button on the Userform, and running the code after you've entered the required Data in the TextBox, as if the code is run from TextBox_Change event as soon enter, any data in it the code will run.
NB:- The code assumes you now have 12 columns!!

Please Login or Register  to view this content.

Regards Mick

Mick it WORKS! I thought this would be impossible. I was thinking the only way you could do it would be, in the with.listbox1 parameter settings, to assign a three dimensional variable to contain (LISTBOX VALUE, THE NUMBER OF ROW FILTERED, THE CELL CONTENTS IN A SPECIFIED CELL ON THAT ROW) and to have this stored for later reference via a quick find, replace algorhythm.

But your way is better by a mile. Thanks so much.

I put the code into the OK Command Button_Click from the UserForm so that it runs once the value has been typed in in the TextBox and a selection made from the Listbox.

I'll test this some more but it looks like it works perfect.

I'm pleased its working for you!!
Regards Mick

Well further testing reveals it does indeed work. But only for one textbox against a listbox in a userform.

I tried to modify the code slightly so that if there are another 2 textboxes, that the text entered is placed in different columns on that row.

Please Login or Register  to view this content.

But this doesn't do anything. No value at all is placed along the row selected in the listbox, in the specified columns.

Hi, I did have a slight problem, because sheet value was a date and I'd placed numbers in the test boxes the results were converted to strange dates, but its now working .
I can't really see why it wouldn't.
Do you get an "Error" or nothing happens.???
Mick