Quick & Dirty Basic Excel Macro To Split Coma Separated Cells Into Columns (ideal for iSAMS sibling exports)

ExcelSplitSiblingName.vba

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
  
'Crude error handling!
On Error Resume Next

siblingNameField = InputBox("Please enter the heading for the sibling names")
If siblingNameField = Empty Then Exit Sub

siblingYearField = InputBox("Please enter the heading for the sibling year group")
If siblingYearField = Empty Then Exit Sub

maximumSibling = InputBox("What is the maximum sibling count for any one family in this data set?")
If maximumSibling = Empty Then Exit Sub

firstBlank = InputBox("Enter first blank column letter?")
If firstBlank = Empty Then Exit Sub


Set nameRng = Range( _
                 Range("A1:Z1").Find(siblingNameField).Offset(1), _
                Range("A1:Z1").Find(siblingNameField).Offset(1).End(xlDown))
                
Set ageRng = Range( _
                 Range("A1:Z1").Find(siblingYearField).Offset(1), _
                Range("A1:Z1").Find(siblingYearField).Offset(1).End(xlDown))
                


'Set Values
nameRng.TextToColumns Destination:=Range(firstBlank + "2"), DataType:=xlDelimited, Comma:=True
ageRng.TextToColumns Destination:=Range(firstBlank + "2").Offset(, maximumSibling), DataType:=xlDelimited, Comma:=True

'Set Headings
For i = 1 To maximumSibling
       Range(firstBlank + "1").Offset(, i - 1).Value = "Sibling Name " + CStr(i)
       Range(firstBlank + "1").Offset(, i - 1 + maximumSibling).Value = "Sibling Year " + CStr(i)
Next i
End Sub