splitting up a string in characters

Posted by Keith on February 11, 2002 5:40 AM

I was able to split up a string in a single cell:
Private Sub CommandButton1_Click()

Range("c1") = Range("a1").Characters(1, 1).Caption
Range("d1") = Range("a1").Characters(3, 1).Caption
Range("e1") = Range("a1").Characters(5, 1).Caption
Range("f1") = Range("a1").Characters(7, 1).Caption
Range("g1") = Range("a1").Characters(9, 1).Caption

End Sub

but how would to do this for 100 or 1000 cells at the same time. Is there a formula you can put into the cells instead of using VB. Or is there a FOR loop I can use that works. Here is an example of a FOR loop that I tried but it doesn't work:

For rwIndex = 1 To 10

aCells = Worksheets("Sheet1").Cells(rwIndex, 1)
cCells = Worksheets("Sheet1").Cells(rwIndex, 3)
dCells = Worksheets("Sheet1").Cells(rwIndex, 4)
eCells = Worksheets("Sheet1").Cells(rwIndex, 5)
fCells = Worksheets("Sheet1").Cells(rwIndex, 6)
gCells = Worksheets("Sheet1").Cells(rwIndex, 7)

cCells = aCells.Characters(1, 1).Text
dCells = aCells.Characters(3, 1).Text
eCells = aCells.Characters(5, 1).Text
fCells = aCells.Characters(7, 1).Text
gCells = aCells.Characters(9, 1).Text

Next rwIndex

Thank you for all your help.

- Keith

Posted by JohnG on February 11, 2002 6:50 AM

Try
For rwIndex = 1 To 10
for ColIncex = 1 to 10
Range(cells(rwindex, colindex).address)= Range("a1").Characters(1, 1).Caption
next Colindex
next rwIndex

Posted by Keith on February 11, 2002 7:53 AM

The code works good but what I want to do is from cells a1 to a100 there is going to be a string like: 1,0,1,0,1. I would like to split up each string form a1 to a100 into corosponding cells: c1 = 1
d1 = 0
e1 = 1
f1 = 0
g1 = 1

I know how to do this with just one cell at a time. but how would you split up all the strings in a1 to a100 at the same time.

maybe make a1 in range("a1") a variable so you can make a FOR loop - range(variable name)?

a1 = 100 c1 = 1 d1 = 0 e1 = 0
a2 = 001 c2 = 0 d2 = 0 e2 = 1
a3 = 111 c3 = 1 d3 = 1 e3 = 1
a4
to a1000

thank for your help so far
- Keith

------------------------------------

Posted by JohnG on February 11, 2002 8:34 AM

Then try
For rwIndex = 1 To 1000
Range("C" & rwindex)= Range("A" & rwindex).Characters(1, 1).Caption
Range("c1") = Range("a1").Characters(1, 1).Caption
Range("D" & rwindex)= Range("A" & rwindex).Characters(3, 1).Caption
Range("E" & rwindex)= = Range("A" & rwindex).Characters(5, 1).Caption
Range("F" & rwindex)= = Range("A" & rwindex).Characters(7, 1).Caption
Range("G" & rwindex)= = Range("A" & rwindex).Characters(9, 1).Caption

next rwIndex

Posted by JohnG on February 11, 2002 8:36 AM

Oooops take out the
Range("c1") = Range("a1").Characters(1, 1).Caption
I left in while I was copying and pasting.

Posted by Keith on February 11, 2002 12:16 PM

Thank You Very Much