Need a userform to do a search and....

[bazofio]

Hi

Im really noob how to use VBA and userforms, but here i found that you guys really make a great work with all of this.
What i need is a userform that when i introduce a number in a box it searches for that precise number in a row interval that have various numbers and if it finds it it will display an average number and a record number that are on others cells, if it doesnīt find that number it will display a message that i will define.

For example i have some numbers in cells A10:F10 and the number that i input on the userform is in C10. What i need is to show me the average number that is on C9 and the record number that is on C11, numbers wich corresponds to the number i input that is on C10. If the number that i put on search in the userform doesnt exist in the A10:F10 interval it will display a message pre-defined.

Sorry for the bad english
and
Thanks in advance

[bazofio]

Anyone please

[tigeravatar]

bazofio,

Welcome to the forum!
How does the code know which cells to search (for example, I'm guessing you want to look in more than just A10:F10)? And is it only searching those cells for exact matches? Will the average always be 1 cell above the found cell and will the record always be 1 cell below the found cell? Will there ever be a time when there is more than one found cell containing the searched number?

[bazofio]

Thanks for the reply tigeravatar

The above was just a example, but the search will be just in one row where there are the exact values that im looking for. The average for the number that i want to search will be 1 cell above and the record always will be 1 cell below.
No tiger, there will just be one found cell with the searched number or there want be any, and then i must introduce that one after and correct the range in the code. The problem is building the code, after that i will try to understand it and make the future changes.......if i can ;-)

[tigeravatar]

bazofio,

Attached is an example workbook based on the criteria you described.
Here is the full userform code:

Please Login or Register  to view this content.

[bazofio]

Wow that was really fast.
Many thanks tigeravatar this is what i need for now, looks like really simple for you ;-).


Regards

[tigeravatar]

You're very welcome

[bazofio]

I have another simple question for you if you can of course.
I already adapt the code to my worksheet and it works brilliant, but i really dont like the way that the msgbox displays the results....it should be something with more "impact". I know that is possible to use a userform in place of the msgbox but in this case i dont know if is possible. Can you please tell me if it is and how can i do that? I can create the userform but i dont know how and where i change the original code to display the results in the new userform instead of the msgbox.

Thanks in advance for your patience :-)

[tigeravatar]

bazofio,

It is possible, just make sure you have a label for the message text, and an OK button that the user can click on to close the form (really they'll be hiding it).
Assuming your messaging userform is named "frmMessage", you would use this code during your main userform's initialize event:

Please Login or Register  to view this content.

Then, in place of the MsgBox code, you would use this:

Please Login or Register  to view this content.

When the user clicks the OK button on frmMessage, its code should be Me.Hide instead of Unload Me.

Lastly, you should also add a terminate event to your main finding userform that will unload the frmMessage userform:

Please Login or Register  to view this content.

[bazofio]

Hi tigeravatar.
I understand your post and what i need to do but what code do i use to show me the information required in the userform? Is the same code that you use to display in the msgbox?
Im talking about the Searched for, Average and record values displayed in the msgbox.

Thanks again for your patience with my ignorance.

[tigeravatar]

Yeah, it would basically be the same code as the msgbox text

[bazofio]

ok tiger, ill give it a try.

Thanks ;-)

[bazofio]

Ok im here again with the last question, i hope.
As i go on with work i see that there were same things that will work great in my worksheet, but i just cant do it. Tiger can you please help with a code, the same one you write to me, but with a option of a listbox or a dropbox in the userform that when i select different names in that listbox/dropbox the userform looks for those values that are in the original code but in different places, i mean in a different sheet with different range. Because maybe i have 14 to 16 sheets where i could use this userform to search for those values (record, average) and it wasnt necessary to make the original code for each sheet.

[tigeravatar]

Something like this should work for you...

Please Login or Register  to view this content.

[bazofio]

And where do i put that code? Attached to the dropdown list or with the userform created?

[tigeravatar]

No, it goes in with the OK/Find button's code

[bazofio]

Sorry tiger but im completely lost here, cant figure it out how to do this. Im really noob at this things :-(

[tigeravatar]

Bazofio,

Attached is a new version of the sample workbook.
I have added a listbox to the userform that is populated with the names of the worksheets.
Each worksheet needs to search a different range, and the designated range to search is determined by the listbox's selection.
Here is the full userform code:

Please Login or Register  to view this content.

[bazofio]

Sorry giving you so much trouble. I adapt your code to my worksheet and itīs working, but i dont know how to change the sheets that i want to use for search. There are all of them in the list box but i want to select the ones that should display. Where do i change that tiger?

Edit: Forget it, i found it. Its in the sheets count, change the number to where it starts counting ;-)

Many thanks tigerland for helping a big noob.