Solved

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

Posted on 2014-02-27
5
143 Views
Last Modified: 2014-02-27
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
Comment
Question by:fordraiders
  • 3
5 Comments
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39892411
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
 
LVL 3

Author Comment

by:fordraiders
ID: 39892835
mystringlength ?
how is that determined ?

Thanks
0
 
LVL 3

Author Comment

by:fordraiders
ID: 39892837
rgonzo..
oop nm.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39892864
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
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 39892882
Thanks
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question