Solved

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

Posted on 2014-02-27
5
140 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 48

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now