SPLITTING TEXT AND FORMATTING !!!

[jay dean]

Hello -

I have 3 questions:

1. How do I use VBA to split text without delimiters into tokens?
Example: Cell A1 contains the text/string "1234". How do I use vba to
split it and place the tokens '1' in B1, '2' in C1, '3' in D1, '4' in
E4? I thought about using the Split() function but these strings don't
have delimiters.

2. When changing the background color of a textbox on a userform, I
used, for example, TextBox1.BackColor = vbBlue. However, that is not the
kind of blue I want. How do I change the code to get a background of one
of the colors in the palette, like navy blue, dark green,e.t.c? Is there
a reference similar to "colorindex" matching a color to a number that I
may use?

3. In Sheet("Z").Range("A1:A20"), I have strings. How do I use vba to
add the contents of each cell (i.e an item) in the range into a textbox
on a userform AND how do I do the same for a listbox on a userform.

Any help would be greatly appreciated.

Thanks
Jay




*** Sent via Developersdex http://www.developersdex.com ***

 

[Bernard Liengme]

Here is a start:

Sub TryMe()
MyString = Range("A1").Value
For j = 1 To Len(MyString)
Cells(1, j + 1) = Mid(MyString, j, 1)
Next j
End Sub

best wishes

 

[Patrick Molloy]

1)

Use MID
b1 = mid("1234",1,1)
c1 = mid("1234",2,1)
d1 = mid("1234",3,1)
e1 = mid("1234",4,1)

2)
try a for/next loop using 1 to 50 and color cells in a sheet to see whats
available

3)
dim cell as range
dim text as string
for each cell in range("A1:A20").Cells
text = text & "," & cell.Value
next
TextBox1.Text = MID(text,2) ' drop preceding ","

 

[Ron Rosenfeld]

Hello -

I have 3 questions:

1. How do I use VBA to split text without delimiters into tokens?
Example: Cell A1 contains the text/string "1234". How do I use vba to
split it and place the tokens '1' in B1, '2' in C1, '3' in D1, '4' in
E4? I thought about using the Split() function but these strings don't
have delimiters.

If no delimiter is specified, then you need to step through each character one
at a time, and assign it to an array element (to sort of duplicate the split
function.

For example:

=====================================
Option Explicit
Sub SplitString()
Dim i As Long
Dim s As Variant
s = xSplit("abcde")
For i = 0 To UBound(s)
Debug.Print i + 1, s(i)
Next i
End Sub
'-----------------------------------------
Function xSplit(s As String, Optional Delimiter As String = "") As Variant
Dim sTemp()
Dim i, j
Select Case Delimiter
Case Is = ""
j = Len(s) - 1
ReDim sTemp(0 To j)
For i = 0 To j
sTemp(i) = Mid(s, i + 1, 1)
Next i
Case Else
sTemp = Split(s, Delimiter)
End Select
xSplit = sTemp
End Function
==================================

--ron

 

[Rick Rothstein]

1. Providing your text is made up of "normal" (that is, non-Unicode
characters with ASCII values of less than 256), then there is a non-looping,
one-line alternative to the Mid function looping code the other respondents
have given you...

C = Split(Trim(Replace(StrConv(YourText, vbUnicode), Chr(0), " ")))

Just assign your text to the YourText variable, execute the code above and
the C variable (which could be declared either as a Variant or as a dynamic
String array) will contain an array in which each of its elements contain a
character from the text you assigned. For example...

Sub TestCharacterSplitting()
Dim YourText As String, C As Variant, X As Long
YourText = "1234"
C = Split(Trim(Replace(StrConv(YourText, vbUnicode), Chr(0), " ")))
' Show the contents of the array of characters in C
For X = 0 To UBound(C)
Debug.Print "<" & C(X) & ">"
Next
End Sub

will print each of the digits in "1234" on its own line with angle brackets
around it (to show you only one character is assigned to each element of the
array) in the Immediate window of the VB editor.

 

[jay dean]

Thank you Rick, Ron, Patrick, and Bernard !!
I really appreciate this.

Jay Dean

*** Sent via Developersdex http://www.developersdex.com ***