I am trying to split a string and create a loop for going through the cells in the column.There are a few challenges:
Split works for ActiveCell
only.
Loop goes through all cells until LastRow but populates all cells
with split string values from ActiveCell
only.
Split of Array starts with i = 0
even though there is Option Base 1
at the beginning of the Module.
How can I change the location of destination (e.g. instead of splitting string next to existing data, is there an option to manage column numbers)?
Thank you
Option Explicit
Option Base 1
Sub SplitStringLoop()
Dim txt As String
Dim i As Integer
Dim y As Integer
Dim FullName As Variant
Dim LastRow As Single
ReDim FullName(3)
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
txt = ActiveCell.Value
FullName = Split(txt, "-")
For y = 2 To LastRow
For i = 1 To UBound(FullName)
Cells(y, i + 1).Value = FullName(i)
Next i
Next y
End Sub
Chris Nelisen outlined the reasons, I had this code written before he posted, so I'll post it anyway.
Option Explicit
Sub SplitStringLoop()
Dim txt As String
Dim i As Integer
Dim y As Integer
Dim FullName As Variant
Dim LastRow As Single
ReDim FullName(3)
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For y = 2 To LastRow
Cells(y, 1).Select
txt = ActiveCell.Value
FullName = Split(txt, "-")
For i = 0 To UBound(FullName)
Cells(y, i + 2).Value = FullName(i)
Next i
Next
End Sub