Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • Last Modified:

add additional parameters to token up to 10 expand on optional terms

excel 2010 vba
USERFORM
TEXTBOX

What I have:
When i type in the textbox a string
I'm setting up the tokens in preparation for an sql variable in Terdata.

Each token is placed in a '%QUICK%'
followed by a <comma> if more tokens are in the textbox

Example:
If i type in the textbox
QUICK MIXER LOAD
the code puts this output

'%QUICK%','%MIXER%', '%LOAD%'

What I need:
I need the code to ONLY ACCEPT 1 TO 10 Tokens
Example:
If i type in the textbox
QUICK MIXER LOAD
the current code puts this output
'%QUICK%','%MIXER%', '%LOAD%'

I need it to do this:
'%QUICK%','%MIXER%', '%LOAD%','%%','%%','%%','%%','%%','%%','%%'
(in this case i needed 7 seperated by a <comma>  (but NO comma ta the end.)
Add the additional '%%'   as needed up to 10

Example 2 :
If i type in the textbox
QUICK MIXER LOAD
the current code puts this output
'%SOCKET%','%WRENCH%'

I need it to do this:
'%SOCKET%','%WRENCH%','%%','%%','%%','%%','%%','%%','%%','%%'

Add the additional '%%'   as needed up to 10
IN THIS CASE I NEED TO ADD 8  '%%'

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


' EXECUTE XREF.d_search ('%%','%%', '%QUICK%','%MIXER%','%LOAD%','%%','%%','%%','%%','%%')
' how many items are in the box ?  build an array
            cArrSql = Split(cD, " ")
If UBound(cArrSql) = 0 Then
 Nf1 = Trim(Chr(39) & "%" & cArrSql(0) & "%" & Chr(39))
Else


                For x = LBound(cArrSql) To UBound(cArrSql)

                        If x = UBound(cArrSql) Then

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

Nf1 = Nf1


MsgBox Nf1, vbCritical

Open in new window



MY TERDATA execute statement
EXECUTE XREF.d_search ('%%','%%', '%QUICK%','%MIXER%','%%','%%','%%','%%','%%','%%')

Thanks
fordraiders
0
Fordraiders
Asked:
Fordraiders
  • 3
1 Solution
 
Rgonzo1971Commented:
Hi,

pls try

Sub macro()
myString = "'%QUICK%','%MIXER%', '%LOAD%'"
myStrLength = UBound(Split(myString, ",")) + 1
AddString = Application.WorksheetFunction.Rept(",'%%'", 10 - myStrLength)
myString = "%QUICK%','%MIXER%', '%LOAD%" & AddString

End Sub

Open in new window

Regards
0
 
FordraidersAuthor Commented:
mystringlength ?
how is that determined ?

Thanks
0
 
FordraidersAuthor Commented:
rgonzo..
oop nm.
0
 
regmigrantCommented:
similar approach using a function

Function stringy(strInput As String)
strtarget = UBound(Split(strInput, ",")) + 1
If strtarget > 10 Then
    stringy = "Error - exceeded 10"
Else
    padding = Application.WorksheetFunction.Rept(",'%%'", 10 - strtarget)
    
    stringy = strInput & padding
End If

End Function

Sub test()
Dim strtest As String
strtest = "%QUICK%','%MIXER%','%LOAD%'"
Debug.Print stringy(strtest)

strtest = "%QUICK%','%MIXER%','%LOAD%','%QUICK%','%MIXER%','%LOAD%','%QUICK%','%MIXER%','%LOAD%','%QUICK%','%MIXER%','%LOAD%"
Debug.Print stringy(strtest)

End Sub

Open in new window

0
 
FordraidersAuthor Commented:
Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now