create wild card string from tokens

excel 2010 vba

userform
textbox

What I need:

if the textbox contains more than  1 token the code works fine:
example:
claw hammer 16    becomes
%claw%hammer%16%

Problem:
If the textbox contains only one word:
HAMMER

it only becomes:
hammer%

I need:
%hammer%

Thanks
fordraiders


Dim Nf1 As String
Dim cArrSql As Variant
Dim cD As String
Dim x As Integer
cD = UserForm2.TextBox33

' how many items are in the box ?  build an array
            cArrSql = Split(cD, " ")
            
            
            
            Dim w As String
            ' new CODE FOR idata description match
           ' 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


Nf1 = Nf1

MsgBox Nf1, vbCritical

Open in new window

LVL 3
FordraidersAsked:
Who is Participating?
 
Pratima PharandeConnect With a Mentor Commented:
try this
Dim Nf1 As String
Dim cArrSql As Variant
Dim cD As String
Dim x As Integer
cD = UserForm2.TextBox33

' how many items are in the box ?  build an array
            cArrSql = Split(cD, " ")
            
            
            
            Dim w As String
If UBound(cArrSql) = 0 Then
 Nf1 = Trim("%" & cArrSql(0) & "%")
Else


            ' new CODE FOR idata description match
           ' 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 

Open in new window

0
 
[ fanpages ]Connect With a Mentor IT Services ConsultantCommented:
Hi,

Why not, simply...

MsgBox "%" & Replace(Trim$(UserForm2.TextBox33), " ", "%") & "%", vbCritical

or

MsgBox "%" & Join(Split(UserForm2.TextBox33, " "), "%") & "%", vbCritical

?

BFN,

fp.
0
 
FordraidersAuthor Commented:
Thanks very much..Fanpages..this is interesting single line of code.
I will use it for other purposes.
0
 
[ fanpages ]IT Services ConsultantCommented:
You're very welcome :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.