Need an advice for searching function

[rong3]

Hello, I am a new VBA programmer and need advice of all you for my very first excel VBA job required by my company.
There is a file with two sheets that as UserForm and Product (which contains data).
I am about to search value on Product by inputting by user at UserForm sheet. So that if the value match when clicking "Search" button then Goto the Range that matches value on Product.
But the matter is the Product code is designed of many types like "73521005-06-07"( the input searching must have not "-" and single, it is shorted by 73521005,73521006,73521007 )
The more detail in the file attached below. It drives me stuck. So i really need all your help for the first VBA. Thank you so so much.
20561

[mdmackillop]

Here is a Combobox solution. Trying to do this with a Textbox is fraught with problems in my opinion.
Your data has duplicate entries. I've made no attempt to deal with that.

[p45cal]

Try the attached.
Lots of assumptions made.

[rong3]

A big thank you for great solution, thank you for all.

[rong3]

Try the attached.
Lots of assumptions made.
Hi , i dectected if in the Product Sample sheet has value like 7103A104-A105 will cause error debug. The code error at here

DigitCount = Len(zz(UBound(zz)))
For i = CLng(Right(zz(0), DigitCount)) To CLng(zz(UBound(zz)))
So i think the code should be removed the alphabet chars.

[p45cal]

Yes, one of the assumptions is that there'd only be numbers after the delimiter (- or,).
You'd have expected this entry to be 7103A104-105.
But what if next time we see an entry like 7103A114-C156?
I will look at correcting where the delimiter has been put, but I won't have much time in the next day or two.

The real trouble is that the raw source data is highly inconsistent; I can foresee a continuous stream of work-arounds being needed.
It is the source data that needs to be sorted.

[rong3]

Yes, one of the assumptions is that there'd only be numbers after the delimiter (- or,).
You'd have expected this entry to be 7103A104-105.
But what if next time we see an entry like 7103A114-C156?
I will look at correcting where the delimiter has been put, but I won't have much time in the next day or two.

The real trouble is that the raw source data is highly inconsistent; I can foresee a continuous stream of work-arounds being needed.
It is that that needs to be sorted.
Hi, thank for supporting me a lot, I tried to fix by below code

Dim RegEx As Object
Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Global = True
RegEx.Pattern = "[A-Za-z]"
zz(UBound(zz)) = RegEx.Replace(zz(UBound(zz)), "")
DigitCount = Len(zz(UBound(zz)))
For i = CLng(Right(zz(0), DigitCount)) To CLng(zz(UBound(zz)))

By that way. it removed all alphabet char and skip to correct address.
I totally agreed about the raw source data is highly inconsistent. It makes me stuck on it during this week.
So i wondered if the source data is bigger, seems about over 1000 rows, does this logic excutes a lot of time, if yes , can you suggest me a solution to optimaze when data is big.
Thanks so much for spending your time.

[p45cal]

A quick look at your RegEx idea seems to be OK.
It would trip up on something like 7103A104-3A105 'though.
Regarding time with 1000 rows - you need to test it. If it's long, we can look at doing what mdmckillop did and establish the array once on activation of that sheet. I suspect it might be fast enough as it is.

[mdmackillop]

My big concern would be a fumble finger typist like me getting an 8 digit number correct.

[rong3]

A quick look at your RegEx idea seems to be OK.
It would trip up on something like 7103A104-3A105 'though.
Regarding time with 1000 rows - you need to test it. If it's long, we can look at doing what mdmckillop did and establish the array once on activation of that sheet. I suspect it might be fast enough as it is.
The main raw source is currently about 600 rows, and it takes approximately 5 - 7 seconds. I have just finished code integrated into my main source code as Worksheet_Changed Event and it looks fine now.
So can you help me about this: The required of logic is not change but now, I want to split 2 sheets out to 2 workbooks, the one is UserForm, last is "Product Sample". How to active and skip to corect address of the "Product Sample" workbook when detected same value.

[p45cal]

You'd better update us with versions of the workbooks involved and what you've got so far, otherwise we'd just be guessing, and probably wrongly.

[rong3]

You'd better update us with versions of the workbooks involved and what you've got so far, otherwise we'd just be guessing, and probably wrongly.
The logic is not change, only split out to 2 workbooks, I wondered that how to do in searching on workbook1 and the return result at workbook 2.
Here is 2 wbs.

[p45cal]

There's no sense in having the same code in both workbooks.
In the attached, code only in xlsm file.
Both files need to be open in the same instance of Excel.
On my old machine, it look less then 1 second to come up with results.
If it is taking significantly longer with your setup, then we'll have to move to create a searchable array somewhere.

[rong3]

There's no sense in having the same code in both workbooks.
In the attached, code only in xlsm file.
Both files need to be open in the same instance of Excel.
On my old machine, it look less then 1 second to come up with results.
If it is taking significantly longer with your setup, then we'll have to move to create a searchable array somewhere.

Maybe the raw source of yours is too small and it takes no much time than the main source, But I just fixed by ignoring the rows null or with space or first letter of good code is alphabet, the more reason takes its slow that I clear a paragraph of code at "SpecialCells(2)", and it is faster now, over 1000 rows now only take 1-2 second at all.
EDIT: So there is no way to excute if one of both files is not open the same Excel?

[mdmackillop]

So there is no way to excute if one of both files is not open the same Excel?
There are two recent posts regarding working between different instances of Excel
http://www.vbaexpress.com/forum/showthread.php?60889-VBA-Copy-and-Past-between-different-instances&p=370263&viewfull=1#post370263
http://www.vbaexpress.com/forum/showthread.php?60682-application-windows-object&p=368909&viewfull=1#post368909

[p45cal]

So there is no way to excute if one of both files is not open the same Excel?
Well, if the only workbook open is Product_Sample , you'd have no textbox or button to press! If the only workbook open is Userform , the code could (with more complex code) find the data in the other workbook, but going back to basics and the requirement in your first post: "then Goto the Range that matches value", I don't see how this can be achieved without opening the second workbook!

[rong3]

Well, if the only workbook open is Product_Sample , you'd have no textbox or button to press! If the only workbook open is Userform , the code could (with more complex code) find the data in the other workbook, but going back to basics and the requirement in your first post: "then Goto the Range that matches value", I don't see how this can be achieved without opening the second workbook!
"then Goto the Range that matches value" is for checking the value is match or not, I have just solved that required open workbook before using it. Thank so much p45cal for helping me a lot during this week.

[p45cal]

is for checking the value is match or notIf only checking for a match then try the attached.
The first time you search for a value it asks you to identify the Product Sample file, the file is not opened in Excel, subsequent searches don't need that and are much faster.
See comments in code too.

[rong3]

If only checking for a match then try the attached.
The first time you search for a value it asks you to identify the Product Sample file, the file is not opened in Excel, subsequent searches don't need that and are much faster.
See comments in code too.
Thanks so much @p45cal, I have already solved this problem.

[rong3]

Hi @p45cal, Sorry for awaking the sleeping theard, but I get trouble on some product code, it always return at the first row, here is attached file and your old solution, Can you take a quick look and help me, thanks so much.
20922

[p45cal]

Test this:
Sub blah()
Dim AllCodes(), Addresses(), FoundCells As Range
ReDim AllCodes(1 To 1)
ReDim Addresses(1 To 1)
For Each cll In Sheets("Product Sample").Columns(1).SpecialCells(2).Cells
'Debug.Print cll.Address(0, 0), cll.Value
cll.Value = Replace(cll.Value, " ", "")
zz = Split(cll.Value, "-")
If UBound(zz) = 0 Then zz = Split(cll.Value, ",") 'either it's split with - or comma, not both.
' Debug.Assert cll.Row <> 56
Select Case UBound(zz)
Case 0
'Debug.Print zz(0), cll.Address(0, 0)
J = J + 1
ReDim Preserve AllCodes(1 To J)
ReDim Preserve Addresses(1 To J)
AllCodes(J) = zz(0)
Addresses(J) = cll.Address(0, 0)
Case Is > 0
'Stop
Dim RegEx As Object
Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Global = True
RegEx.Pattern = "[A-Za-z]"
zz(UBound(zz)) = RegEx.Replace(zz(UBound(zz)), "")
DigitCount = Len(zz(UBound(zz)))

For i = CLng(Right(zz(0), DigitCount)) To CLng(zz(UBound(zz)))
'Debug.Print Left(zz(0), Len(zz(0)) - DigitCount) & Format(i, Left("000000000", DigitCount)), cll.Address(0, 0)
J = J + 1
ReDim Preserve AllCodes(1 To J)
ReDim Preserve Addresses(1 To J)
AllCodes(J) = Left(zz(0), Len(zz(0)) - DigitCount) & Format(i, Left("000000000", DigitCount))
Addresses(J) = cll.Address(0, 0)
Next i
End Select
Next cll
Debug.Print J
FindMe = Sheets("UserForm Search").TextBox1.Value
For i = LBound(AllCodes) To UBound(AllCodes)
If AllCodes(i) = FindMe Then
If FoundCells Is Nothing Then
Set FoundCells = Sheets("Product Sample").Range(Addresses(i))
Else
Set FoundCells = Union(FoundCells, Sheets("Product Sample").Range(Addresses(i)))
End If
End If
Next i
If Not FoundCells Is Nothing Then
Application.Goto FoundCells
MsgBox FindMe & " found in cells " & FoundCells.Address(0, 0)
Else
MsgBox "Not found"
End If
End Sub

[rong3]

Hi, Can you explain this for me, I am not clear at this, why must using Union.


Set FoundCells =Union(FoundCells, Sheets("Product Sample").Range(Addresses(i)))

When finding this "7103A403" , this returns as below ( A2:A3,A7), the result "A7" is correct , and the '7103A346' must returns "A3" , not "A2:A3".

20938

[p45cal]

As far as I can see A2 is also correct; the prefix 7103A is correct and 403 is in the range 345 to 438.
When looking for 346, that too is in A2 and A3; the 2nd element of A2 and the 1st of A3.
UNION so that I can select them at the end to show where they are rather than loop through them one at a time. You don't have to use it.

[rong3]

As far as I can see A2 is also correct; the prefix 7103A is correct and 403 is in the range 345 to 438.
When looking for 346, that too is in A2 and A3; the 2nd element of A2 and the 1st of A3.
UNION so that I can select them at the end to show where they are rather than loop through them one at a time. You don't have to use it.

Oh seems I am not explain clearly the symbol "-", sorry for taking your time, but the "7103A345-438" is only 2 elements that stands for "7103A345" and "7103A438", it is not a range from 345 to 438.
Can you help me to fix it? Thanks too much.

[p45cal]

Oh seems I am not explain clearly the symbol "-", sorry for taking your time, but the "7103A345-438" is only 2 elements that stands for "7103A345" and "7103A438", it is not a range from 345 to 438.
Can you help me to fix it? Thanks too much.oh groan… and what does the comma mean? How many elements in 7103A111,120?

[rong3]

oh groan… and what does the comma mean? How many elements in 7103A111,120?
All the code of product is format by "-", if you see others like "," or "->" it will be replaced in code to "-", so it not a range as you think.
How many elements in 7103A111,120? It just 2 that stands for 7103A111 and 7103A120.

[p45cal]

Can you help me to fix it?Yes, I'll get round to it soon.

[p45cal]

Test the following which is in the attached.
Sub blah()
Dim AllCodes(), Addresses(), FoundCells As Range
ReDim AllCodes(1 To 1)
ReDim Addresses(1 To 1)
For Each cll In Sheets("Product Sample").Columns(1).SpecialCells(2).Cells
cll.Value = Replace(cll.Value, " ", "")
zz = Split(cll.Value, "-")
If UBound(zz) = 0 Then zz = Split(cll.Value, ",") 'either it's split with - or comma, not both.
Select Case UBound(zz)
Case Is >= 0
J = J + 1
ReDim Preserve AllCodes(1 To J)
ReDim Preserve Addresses(1 To J)
AllCodes(J) = zz(0)
Addresses(J) = cll.Address(0, 0)
If UBound(zz) > 0 Then
For i = 1 To UBound(zz)
J = J + 1
ReDim Preserve AllCodes(1 To J)
ReDim Preserve Addresses(1 To J)
AllCodes(J) = Mid(zz(0), 1, Len(zz(0)) - Len(zz(i))) & zz(i)
Addresses(J) = cll.Address(0, 0)
Next i
End If
End Select
Next cll
FindMe = Sheets("UserForm Search").TextBox1.Value
For i = LBound(AllCodes) To UBound(AllCodes)
If AllCodes(i) = FindMe Then
If FoundCells Is Nothing Then
Set FoundCells = Sheets("Product Sample").Range(Addresses(i))
Else
Set FoundCells = Union(FoundCells, Sheets("Product Sample").Range(Addresses(i)))
End If
End If
Next i
If Not FoundCells Is Nothing Then
Application.Goto FoundCells
MsgBox FindMe & " found in cells " & FoundCells.Address(0, 0)
Else
MsgBox "Not found"
End If
End Sub

[rong3]

Thank you @p45cal so much, it works perfectly now.: pray2: