VBA: Split TextBox Value 8 Char Length into Each Active Cell

[excelnubz]

I'd like to split the textbox value in groups of 8 characters into each empty active unlocked cell (column A and Column B) after the user clicks on the submit button in the UserForm.
So, basically parse the first 8 characters into the first active cell, move to the next cell then parse the next 8 characters into that active cell, and so forth.

Here's what I have so far...

Private Sub CommandButton1_Click()

    Dim myScan As String, cell As Range
    
    myScan = TextBox1.Value
    
    If myScan = "" Then
        MsgBox ("No Input")
    Else
        ActiveCell.Value = UserForm1.TextBox1.Value
        
        For Each cell In Range("A2:B31")
            ActiveCell.Value = Split(UserForm1.TextBox1.Value, "", 8)
        Next cell
        
    End If
    
    Unload Me
    
End Sub

I feel like I'm not using the Split function right in my code (I'm trying to say split textbox value, no delimiter, 8 characters in length)
and I'm missing some kind of "i" statement.

Here's a visual of what I'm looking for:

Before Form Submit ^

After Form Submit ^

 

[mikerickson]

Dim i as Long

i = 0
For Each cell In Range("A2:B31")
    ActiveCell.Value = Mid(UserForm1.TextBox1.Value, 8*i+1, 8)
    i = i + 1
Next cell

 

[Peter_SSs]

I don't know what your active cell is but see if this heads yo in the right direction. It puts 8 characters in A1, A2, A3, until myScan has no characters left

Do Until Len(myScan) = 0
  i = i + 1
  Range("A" & i).Value = Left(myScan, 8)
  myScan = Mid(myScan, 9)
Loop

 

[excelnubz]

Dim i as Long

i = 0
For Each cell In Range("A2:B31")
    ActiveCell.Value = Mid(UserForm1.TextBox1.Value, 8*i+1, 8)
    i = i + 1
Next cell

So it compiles fine but when I run the code, nothing seems to record in the cells (they stay blank), but maybe it's my code, here it is:

Dim myScan As String, cell As Range
    Dim i As Long
    
    myScan = TextBox1.Value
    i = 0
    
    If myScan = "" Then
        MsgBox ("No Input")
    Else
        For Each cell In Range("A2:B31")
            ActiveCell.Value = Mid(UserForm1.TextBox1.Value, 8 * i + 1, 8)
            i = i + 1
        Next cell
    End If
    
    Unload Me

 

[excelnubz]

I don't know what your active cell is but see if this heads yo in the right direction. It puts 8 characters in A1, A2, A3, until myScan has no characters left

Do Until Len(myScan) = 0
  i = i + 1
  Range("A" & i).Value = Left(myScan, 8)
  myScan = Mid(myScan, 9)
Loop

So, this one seems to repeat the 8th character infinitely down column A and B, but maybe its the way I've applied it to my code:

Dim myScan As String, cell As Range
    Dim i As Long
    
    myScan = TextBox1.Value
    i = 0
    
    If myScan = "" Then
        MsgBox ("No Input")
    Else
        Do Until Len(myScan) = 0
            i = i + 1
            Range("A2:B31" & i).Value = Left(myScan, 8)
            myScan = Mid(myScan, 9)
        Loop
    End If
    
    Unload Me

Here's a snapshot of the results:

 

[Peter_SSs]

but maybe its the way I've applied it to my code:

Yes, the problem is the way you have applied it.

Try this within your code.
I have assumed that the 60 cells in A2:B31 is enough to hold all the characters.

i = 0
j = 1
With Range("A2:B31")
  Do Until Len(myScan) = 0
    i = i + 1
    If i > .Rows.Count Then
      i = 1
      j = j + 1
    End If
    .Cells(i, j).Value = Left(myScan, 8)
    myScan = Mid(myScan, 9)
  Loop
End With

 

[excelnubz]

Yes, the problem is the way you have applied it.

Try this within your code.
I have assumed that the 60 cells in A2:B31 is enough to hold all the characters.

i = 0
j = 1
With Range("A2:B31")
  Do Until Len(myScan) = 0
    i = i + 1
    If i > .Rows.Count Then
      i = 1
      j = j + 1
    End If
    .Cells(i, j).Value = Left(myScan, 8)
    myScan = Mid(myScan, 9)
  Loop
End With

This worked thank you!

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.

 

[mikerickson]

So it compiles fine but when I run the code, nothing seems to record in the cells (they stay blank), but maybe it's my code, here it is:

Dim myScan As String, cell As Range
    Dim i As Long
   
    myScan = TextBox1.Value
    i = 0
   
    If myScan = "" Then
        MsgBox ("No Input")
    Else
        For Each cell In Range("A2:B31")
            ActiveCell.Value = Mid(UserForm1.TextBox1.Value, 8 * i + 1, 8)
            i = i + 1
        Next cell
    End If
   
    Unload Me

My bad my code should be cell not ActiveCell