PDA

View Full Version : [SOLVED] VBA search



Ken123
04-13-2017, 12:15 AM
Hi Guys, I have a Excel table consist of Customer Name and Account.Example: John Doe (in A1) and 123 (in A2). Then based on bank reference ( 0000100 FT transfer 00100 John 000100 ) i woud like to have a VBA code that will seach John from then bank reference and match it to the table, Once matched, return the Customer full name and account number. hope i make myself clear. and look forward to any discussion and solution. cheers

mancubus
04-13-2017, 01:55 AM
welcome to the forum.

upload a sample file to work with.
provide the desired output in a blank sheet.

Ken123
04-13-2017, 02:31 AM
welcome to the forum.

upload a sample file to work with.
provide the desired output in a blank sheet.

Ken123
04-13-2017, 02:33 AM
hi mancubus, thank you for your reply, have attached as book 1.

mancubus
04-13-2017, 04:21 AM
Regarding the input-output, you only need VLOOKUP.


you have a named range called CUST

lookup values are in Column A (starting at A5)
lookup table is CUST
Customer details are in the 3rd column of CUST
Accounts are in the 4th column of CUST

Formula in B5

=VLOOKUP(A5,CUST,3,0)

Formula in C5

=VLOOKUP(A5,CUST,4,0)

copied down to desired row.

Ken123
04-20-2017, 01:53 AM
Regarding the input-output, you only need VLOOKUP.


you have a named range called CUST

lookup values are in Column A (starting at A5)
lookup table is CUST
Customer details are in the 3rd column of CUST
Accounts are in the 4th column of CUST

Formula in B5

=VLOOKUP(A5,CUST,3,0)

Formula in C5

=VLOOKUP(A5,CUST,4,0)

copied down to desired row.







Hi mancubus,

thank you for your responses, and sorry i have make an mistake, there should not be any bank ref column in the name range (cust) thus vlookup is not usable.

i have attached a corrected excel for your easy reference.

mancubus
04-20-2017, 04:28 AM
please dont quote previous messages unless you need highlighting a specific bit. in that case just qoute that small bit not the whole message.


that said, you are trying to find partial string from column A in column F.


since there is no common pattern for partian strings, according tome, the best way is to use a helper column and enter corresponding values from column F manually. this enables using VLOOKUP function.

i did it for you in the attached file.

Column D in "task" sheet is the helper column.

if you can set up a pattern then we can provide a either formula or vba solution without using the helper column.

examine the range A21:C29 and its header on "task" sheet.

good luck.

Ken123
04-21-2017, 12:25 AM
Hi Mancubus,

Once again, thank you so much for guiding me thru.

Yes the LOOKUP NAMES in the Detail Description is not consistent at their position.

So i am thinking (but not sure if is feasible) is that i will set up a look up table. so everything there is the Detail Description, Example: < BENDIGO BANK 0000000000000000 DELANY ADVERT BAL > then the code will search the look up table one by one till they find < DELANY > so is probably using a loop with 2 exit condition ( when a match is found then return something and when the search finish the whole look up table and return nothing)

mancubus
04-21-2017, 02:09 AM
you are welcome.

yes. i oftenly do that.
when the formula returns N/A error in a cell, i understand there new items to add, and update the look up table accordingly.

in that case vlookup will produce the desired results.

Ken123
04-24-2017, 02:19 AM
Mancubus,

can u look at this excel and see if my logic is correct and whether is feasible?

thanks alot^^

mancubus
04-24-2017, 04:52 AM
keep the table and the named range (CUST) in your file. because the following code uses them.
update this table so that it will cover all the (partial) values in column A.



Sub vbax_59174_find_search_list_in_column_return_corresponding_info()

Dim srcList
Dim foundCell As Range
Dim i As Long

srcList = Range("CUST").Value

With Worksheets("task")
For i = LBound(srcList) To UBound(srcList)
Set foundCell = .Columns(1).Find(What:=srcList(i, 1), _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not foundCell Is Nothing Then
foundCell.Offset(, 1).Value = srcList(i, 2)
foundCell.Offset(, 2).Value = srcList(i, 3)
End If
Next i
End With

End Sub

Ken123
04-26-2017, 02:05 AM
Mancubus,

Thank you so much, took me so long to understand your code. its really amazing. will have to study more on VBA.

much appreciated.

Cheers
Ken

mancubus
04-26-2017, 04:15 AM
you are welcome.

pls mark the thread as solved from Thread Tools dropdown for future references to the thread.

Ken123
04-26-2017, 11:35 PM
Mancubus,

sorry, i have some trouble with the code,

lets say i expand and have DYMOCKS BRISBANE0000000000000000 DY0024 at cell A10, the code will not reflect the result at B10 and C10.

not sure if i fully understand the code. hope u can explain a little to me.

Many Thanks

Cheers
Ken

mancubus
04-27-2017, 12:52 AM
so the values in column A are not unique?
there are multiple occurences of a "Detail Description"?

Ken123
04-27-2017, 01:08 AM
Yes it will expand. and have duplicates... also in the future, i will expand the look up table as well.. so will have more information.

mancubus
04-27-2017, 02:04 AM
this may be called classic to do a 'multiple instance search' as you can find it anywhere..



Sub vbax_59174_find_search_list_in_column_return_corresponding_info_multi_insta nce()

Dim srcList
Dim FoundCell As Range
Dim i As Long
Dim FirstAddress As String
srcList = Range("CUST").Value

With Worksheets("task")
For i = LBound(srcList) To UBound(srcList)
Set FoundCell = .UsedRange.Columns(1).Find(What:=srcList(i, 1), _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext)

If Not FoundCell Is Nothing Then
FirstAddress = FoundCell.Address
End If

Do Until FoundCell Is Nothing
FoundCell.Offset(, 1).Value = srcList(i, 2)
FoundCell.Offset(, 2).Value = srcList(i, 3)
Set FoundCell = .UsedRange.Columns(1).FindNext(After:=FoundCell)
If FoundCell.Address = FirstAddress Then Exit Do
Loop
Next i
End With

End Sub

Ken123
04-29-2017, 10:40 PM
mancubus,

this is so cool!!! works perfectly! thank you so much^^

appreciate your kind assistance.

Cheers
Ken

mancubus
04-30-2017, 11:21 AM
welcome.
glad it helped.