PDA

View Full Version : Extracting 4 digit number from combobox, looking that up in a worksheet column etc.



bensdb
02-01-2014, 04:13 PM
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!!

Bob Phillips
02-01-2014, 05:31 PM
This will telll you the number found, as long as it is always 4 digits


MsgBox "Number found = " & Application.Evaluate("MATCH(1,--ISNUMBER(FIND(ROW(1000:9999),""" & cmbkids.Value & """)),0)+999")

bensdb
02-02-2014, 01:41 AM
This will telll you the number found, as long as it is always 4 digits


MsgBox "Number found = " & Application.Evaluate("MATCH(1,--ISNUMBER(FIND(ROW(1000:9999),""" & cmbkids.Value & """)),0)+999")


Wow thank. That's great start! Now I just need to figure out how to lookup that variable in column a of my worksheet and I think I will be able to work out the rest myself!

So vba doesn't have a 'lookup' function does it? What is it's equivalent?

mancubus
02-02-2014, 02:47 AM
hi.

you can assign it to a variable and use range.Find method to find its cell.



Private Sub CommandButton1_Click()
Dim itmSearch
Dim FoundCell As Range

itmSearch = Application.Evaluate("MATCH(1,--ISNUMBER(FIND(ROW(1000:9999),""" & cmbkids.Value & """)),0)+999")
Set FoundCell = Worksheets("KidsDB").Columns(1).Find(itmSearch)
FoundCell.Resize(, 3).Copy Worksheets("Register").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub


another way is to use Application.Match to return itmSearch's relative position in a range/array then use that number as foundcell's row number


Private Sub CommandButton1_Click()
Dim itmSearch
Dim RowNum

itmSearch = Application.Evaluate("MATCH(1,--ISNUMBER(FIND(ROW(1000:9999),""" & cmbkids.Value & """)),0)+999")
RowNum = Application.Match(itmSearch, Worksheets("KidsDB").Columns(1), 0)
Worksheets("KidsDB").Range("A" & RowNum & ":C" & RowNum).Copy Worksheets("Register").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub


i assume 4-digit numbers from combobox always exist in Col A of KidsDB. if not, add error handling.

you can google "Excel VBA Find Method" or "Excel VBA Application.Match" to learn more.

another methods can be found/developed. it's up to one's VBA skills and imagination. :)