Extracting 4 digit number from combobox, looking that up in a worksheet column, and then copying 3 columns from the corresponding row to new page.

bensdb

New Member
Joined
Jan 24, 2014
Messages
18
I have a combobox called 'cmbkids' on a form named 'Hideform'. On the press of a button I want to search for a 4 digit number within the string that is currently selected in the combobox. The string might look something like this 'Adam,Cordliffe - 1301 - Round 2', but might also sometimes look like this '1301 - Adam,Cordliffe - Round 2' so the position of the 4 digit number is not fixed.

Once it has found the 4 digit number I need to then lookup that number in column A of worksheet 'KidsDB', and then copy columns A to C of that row and paste to the next available row in another worksheet named 'Register'.

I'm really stuck at this point in my project and any help would be appreciated.

Even if you only have part of the solution please point me in the right direction.

Thanks Guys!!
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Drrellik

Well-known Member
Joined
Apr 29, 2013
Messages
770
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows
{=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))} CTRL+SHIFT+ENTER this a found on another site for stripping the numbers out of a text string. hope this points you in the right direction.
 

bensdb

New Member
Joined
Jan 24, 2014
Messages
18
Thanks but I'm not sure it will in my case because I have two sets of numbers. I need to get just the 4 digit number!
 

bensdb

New Member
Joined
Jan 24, 2014
Messages
18
MsgBox "Number found = " & Application.Evaluate("MATCH(1,--ISNUMBER(FIND(ROW(1000:9999),""" & cmbkids.Value & """)),0)+999")

Right I've now got this bit of cod which extracts a 4 digit number. Now I just need to figure out how to look up that variable in column a of my worksheet, and i think I can figure out the rest myself . vba doesn't have a 'lookup' function does it? What's is equivalent?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,278
Messages
5,600,695
Members
414,400
Latest member
Damocles2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top