excel vba : how to split characters in text file into cells?

[paku]

Hi

I need some opinions. I got a text file loaded into a text box
and a combo box which has several integer values.
When a user choose a value from the combo box, e.g. 3,
I want to be able to group the characters from the text file
into 3 characters where each cell consists of one character.

The sample of the text file looks like this:
WUBEFIQLZURMVOFEHMYMWTIXCGTMPIFKRZUPMVOIRQMMWOZM

And when the user choose the number from the combo box,e.g. 3
I want the cell to contain:


W U B
E F I
Q L Z
U R M
V O F
and so forth

I have this line of codes but I wander if there's a better way of doin
it?

Private Sub KeyLengthComboBox_Change()
Worksheets("Sheet9").Activate
Dim myChar
keylength = CInt(KeyLengthComboBox)

For k = 1 To CipherTextBox.TextLength
myChar = Mid(CipherTextBox.Text, k, 1)
If k Mod keylength = 0 Then
Worksheets("sheet9").Cells(Int(k / keylength), keylength)
myChar
Else
Worksheets("sheet9").Cells(Int(k / keylength) + 1, k Mo
keylength) = myChar
End If
Next k

End Sub

Many thanks
did

 

[Patrick Molloy]

thisrow = 0
mytext = textbox1.value 'eg WUBEFIQLZU...
mystep = combobox1.value 'eg 3

for i = 1 to len(mytext) step mystep

thisrow = thisrow + 1
cells(thisrow ,1) = mid(mytext,i,mystep)

mext

 

[paku]

Thank you Patrick but the desired output is
to be like

one character per each cell instead of 3 characters per cell

for example

W in cell (1,1)
M in cell (1,2)
Y in cell (1,3)
W in cell (2,1)
M in cell 2,2)
Y in cell (2,3)
U in cell (3,1)
H in cell (3,2)
K in cell (3,3)

and so forth


any ideas?

thank

 

[Dave Peterson]

Does this minor modification of Patrick's code do it?

Option Explicit
Sub testme01()

Dim thisRow As Long
Dim thisCol As Long
Dim myText As String
Dim myStep As Long
Dim i As Long

thisRow = 0
'myText = textbox1.Value 'eg WUBEFIQLZU...
'myStep = combobox1.Value 'eg 3

myText = "abcdefghijklmnopqrstuvwxyz"
myStep = 5

For i = 1 To Len(myText)
If (i Mod myStep) = 1 Then
thisRow = thisRow + 1
thisCol = 1
Else
thisCol = thisCol + 1
End If
Cells(thisRow, thisCol) = Mid(myText, i, 1)
Next i
End Sub

 

[Juan Pablo González]

Hi

I need some opinions. I got a text file loaded into a text box
and a combo box which has several integer values.
When a user choose a value from the combo box, e.g. 3,
I want to be able to group the characters from the text file
into 3 characters where each cell consists of one character.

The sample of the text file looks like this:
WUBEFIQLZURMVOFEHMYMWTIXCGTMPIFKRZUPMVOIRQMMWOZM

And when the user choose the number from the combo box,e.g. 3
I want the cell to contain:


W U B
E F I
Q L Z
U R M
V O F
and so forth

An alternate method, without looping, but only works with "small" strings (<
252 characters)

Sub BreakIt()
Dim St As String
Dim HowMany As Long
Dim Nm1 As Name, Nm2 As Name

St = "WUBEFIQLZURMVOFEHMYMWTIXCGTMPIFKRZUPMVOIRQMMWOZM"
HowMany = 3

If Len(St) > 252 Then
MsgBox "Can't do this one...", vbCritical
Exit Sub
End If

Set Nm1 = ThisWorkbook.Names.Add("_TempString", _
"=" & Chr$(34) & St & Chr$(34))
Set Nm2 = ThisWorkbook.Names.Add("_TempForm", _
"=MID(_TempString,ROW($A$1:$A$" & Len(St) & "),1)", True)

With Range("A1").Resize(Application.RoundUp(Len(St) \ HowMany, 0),
HowMany)
.Formula = "=INDEX(_TempForm," & HowMany & "*ROW(A1)-" & _
HowMany & "+COLUMN(A1))"
.Value = .Value
On Error Resume Next
.SpecialCells(xlCellTypeConstants, xlErrors).Value = ""
End With

Nm1.Delete
Nm2.Delete
End Sub

 

[Robert McCurdy]

Just fill down and across as (no vba) needed

=MID(Sheet1!$A2,COLUMN(A1),1)


Regards Robert

 

[paku]

Hi All,

Thanks, Dave. The modification works fine.
Thanks to all of you too for the opinions.

did