Solved

# moving   ANY numeric TERM  to the end of the search term

Posted on 2014-02-26
154 Views
Excel vba:
Userform
Textbox- search term box

In the code below I'm taking all terms and making me a unique string

What I need:

If i type into the textbox a term like

16oz. HAMMER CLAW

my result looks like this now:
%16oz%HAMMER%CLAW%

I need the term to look like:
%HAMMER%CLAW%16oz%

Another Example:
1/4-20 WASHER 5/8 Hex Head Cap Screw

my result looks like this now:

I need the term to look like:

Another Example:
1/4in open end wrench

my result looks like this now:
%1/4in%open%end%wrench%

I need the term to look like:
%open%end%wrench%1/4in%

Here is my current code

Dim Nf1 As String
Dim cArrSql As Variant
Dim cD As String
Dim x As Integer
Dim sSpecialChars As String
Dim i As Long
Dim w As String
cD = UserForm2.TextBox33

' strip special characters first NEW way
sSpecialChars = "!@#\$%^&*()_+-={}|[]\:"";'<>?,./~`"
For i = 1 To Len(sSpecialChars)
cD = Replace\$(cD, Mid\$(sSpecialChars, i, 1), " ")
Next
cD = cD
' THIS EXCEL TRIM FUNCTION
cD = Application.Trim(cD)
cD = cD
' how many items are in the box ?  build an array
cArrSql = Split(cD, " ")

If UBound(cArrSql) = 0 Then
Nf1 = Trim("%" & cArrSql(0) & "%")
Else

' search through the text box requests tokens
For x = LBound(cArrSql) To UBound(cArrSql)

If x = UBound(cArrSql) Then

Nf1 = Trim(Nf1 & cArrSql(x) & "%")
Else
If x = 0 Then
Nf1 = Trim("%" & Nf1 & cArrSql(x) & "%")
Else
Nf1 = Trim(Nf1 & cArrSql(x) & "%")
End If
End If
Next
End If

Nf1 = Nf1

MsgBox Nf1, vbCritical
0
Question by:fordraiders

LVL 49

Accepted Solution

Rgonzo1971 earned 500 total points
ID: 39889580
Hi,

pls try

Sub macro()
Dim arrPosition()
Dim arrNewString()
myString = Mid(myStringTemp, 2, Len(myStringTemp) - 2)
arrString = Split(myString, "%")
ReDim arrPosition(UBound(arrString))

For Idx = LBound(arrString) To UBound(arrString)
If arrString(Idx) Like "*#*" Then
arrPosition(Idx) = True
Else
arrPosition(Idx) = False
End If
Next
ReDim arrNewString(UBound(arrString))
For Idx = LBound(arrString) To UBound(arrString)
If arrPosition(Idx) = False Then
arrNewString(Idx1) = arrString(Idx)
Idx1 = Idx1 + 1
End If
Next
For Idx = LBound(arrString) To UBound(arrString)
If arrPosition(Idx) = True Then
arrNewString(Idx1) = arrString(Idx)
Idx1 = Idx1 + 1
End If
Next
myNewString = "%" & Join(arrNewString, "%") & "%"
End Sub
Regards
0

LVL 3

Author Closing Comment

ID: 39892305
Thanks
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question