Alternative to user form

[ipbr21054]

Morning,
Ive always has a command button so a user form opens & you see text boxes etc.

I’m looking to try the same but on the worksheet & not open a user form.

So as an example.
Cell A1 is a search box.
Cell A2 is the results box
Cells C1:C10 is the range.
Command button to run code.

Can you give me a basic example please of the code.
The range would consist of let’s say animals.
You type Dog in A1 and press command button.
The code looks in the range & returns the cell reference say C6 in the reply box A2

I know it sounds basic but I’ve now idea what it’s called etc to search as I’ve only ever known user forms.

 

[sykes]

In your particular example, one would normally just use a formula, not VBA.
I say in your example, because I'm guessing you're going to be wanting to extend the scope of this eventually.

Formulae can be very powerful, and there are some top folk on here for formulae (as there are for VBA solutions).

One such formula which would work in this case, would be to use the following in A2 (your results cell):

="C" & MATCH(A1,C1:C10,0)

You can copy & paste it into the formula bar (or directly into A2) then use the Return key to enter it.
No need for VBA at all - or even a command button.

I've cheated a bit, by "hardcoding" the text "C" into the formula, as the bare formula only returns the row number of the found-in cell.

A bit basic, but it'll get you started.

 

[ipbr21054]

Thanks,
Basic is good as i thought a command butt was needed,seen this of sheets but ive always done userforms.

So this code of yours.

="C" & MATCH(A1,C1:C10,0)

C is where the range of answers are located.
MATCH is what we want the code to do.
A1 is what we want it to look for in the range of answers.
C1:C10 is the range to look in.
0 at the end is for ????

If you dont mind can you just advise a message etc,i mean if i type Frog & Frog isnt in the list i assume i will see #N/A

Vba would pop up a msgbox FROG not found etc.

Thanks

 

[sykes]

Yes Ian - pretty much correct.

I suggest that you don't use the term "code" as it may confuse others reading your posts. In this case - it's a formula.
"C" is literally the text "C" - because we already know the search-in range is column C. I was being lazy. I'm sure you could have a formula which would return the whole cell address, as per your post.
All your other statements are correct.
The "0" at the end dictates that the formula should return the first found value that is exactly equal to the look_up value.
Here's one of the Microsoft help pages, on the Match function.

This change to the formula, would achieve what you're asking i.e. if "Frog" was typed into A1, then "Frog not found" would be the text returned in A2:
= IFNA(MATCH(A1,C1:C10,0),A1 & " not found")

 

[ipbr21054]

Hmmm
That returned #NAME

I will take a look

Thanks

 

[sykes]

Well - I've reproduced it in a worksheet, and it's working for me.
I've not seen this actual version of the function before, so it may be new - and perhaps only pertinent to newer versions of Office. I'm using 365 - the name of which they've recently changed to "Microsoft 365." It could be that, if you're running an older version of Office, "ISNA" isna included (see what I did there?!!).