Query an Array and list in a User Form

[chrisandsally]

Dear Helpful people!

I have attached a spread sheet that contains dates in the column and peoples initials underneath. I require a query which is done on a userform that you can input the persons initials.

It then queries the spreadsheet with only the people initials with a # in front need to copied into the main output on the userform showing the list of dates.

So in the example if I type AAA in the textbox1 It looks at all dates with #AAA and lists 1/1/2015, 2/1/2015 & 4/1/2015.

Thank You

[stnkynts]

I suggest you create a command button on your form then assign this to the button. Type in the search parameter (withholding the #) then click the button. Should populate the listbox

Please Login or Register  to view this content.

[chrisandsally]

Thank you so much that works a treat! This solves a big problem thank you. What I thought would not matter and I am wrong, should have mentioned in my top post. is that most of the initials have numbers at the end.

For Example:

#AAA6
#AAA15

Some initials are only two characters.

#BB6

The current script ignores the leading # how could the following code be modified to ignore the trailing numbers as well.

Please Login or Register  to view this content.

Thank you, Rep point on the way.

[stnkynts]

Change this line

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

That should do the trick.

[chrisandsally]

Thank you again. Yes that does do the trick. Wow that easy when you know how.

If I could ask one further question. The line

Please Login or Register  to view this content.

This sets presumably sets the range from D5 though to NO (999999 or right to the bottom of the sheet)

If I have two groups of ranges to specifically look in.

For Example D5 to NO43 and D57 to NO78 how do I alter this line or is there more to it.

Thank you once again in advance.

[stnkynts]

Like this:

Please Login or Register  to view this content.

[chrisandsally]

Thank you again!

There is one range in which I am required to find an initial that does not have a # in front but has a number at the end

#AAA6 Do not pass
AAA6 I do want to pass.

What do I need to change in your code?

Please Login or Register  to view this content.

Many thanks for your help again?!

[stnkynts]

There is one range in which I am required to find an initial that does not have a # in front but has a number at the end

I don't think there is a simple solution for this. You will probably have to run multiple loops.

Can you please elaborate on the parameters which trigger this second search?

[chrisandsally]

Thank you stnkynts

Another way that would be perfect for us would be to show the complete query cell along side the date in the ListBox.

So a cell containing either #AAA12 or just AAA12 entered on the date 1/1/2016

The ListBox would contain. (The two results side by side rather than 2 ListBox's would be preferable)

1/1/2016 #AAA12

or

1/1/2016 AAA12

Is this easier?

Your help very much appreciated, thank you.

[stnkynts]

Is this easier?

Not at all. Let me do some testing and see what I come up with.

[stnkynts]

Try this:

Please Login or Register  to view this content.

[chrisandsally]

Thank you again. Spot on.

[chrisandsally]

Hi there, one strange thing is since the latest piece of code the date in the ListBox is in American format of MM/DD/YYYY. Is there a quick parameter to change it to DD/MM/YYYY.

Thank you for your time.