I have written a script, that when I select the column, and hit Ctrl + Shift + S, it will ask me for a delimiter, then based off that it will split the column, but for some reason it is not working. The only error it is giving me is "Object doesn't support this property or method", and I am at a complete loss as of why. Here is my code. I would love insight or direction on how to fix my error.
Thank you.
Code:
Option Explicit
Sub SplitColumn()
Dim oRange As Range, sSeparator As String, iCountSep As Integer
Dim sTest As String, i As Integer, n As Long
Dim sLong As String, sShort As String, sRest As String
Set oRange = Application.InputBox("Select entire column", , Selection.Adddress, , , , , 8)
If oRange.Cells.Count = 1 Then MsgBox "You must select the entire column": Exit Sub
sSeparator = InputBox("What is the separator?", , ",")
sTest = oRange.Cells(2, 1) [COLOR=#008000]'test the first entry,excluding the label or header[/COLOR]
For i = 1 To Len(sTest)[COLOR=#008000] 'An error occurred while executing the command: INVALID-IB-CLASS details: In Basket Classification AH SM Coumadin did not match an active category value.[/COLOR]
If Mid(sTest, i, 1) = sSeparator Then iCountSep = iCountSep + 1
Next i
For i = 1 To iCountSep [COLOR=#008000]'2 commas[/COLOR]
oRange.Offset(0, i).EntireColumn.Insert
Next i
For n = 2 To oRange.CurrentRegion.Cells.Count
With oRange.Cells(n, 1)
sRest = oRange.Cells(n, 1)
[COLOR=#008000] 'An error occurred while executing the command: INVALID-IB-CLASS details: In Basket Classification AH SM Coumadin did not match an active category value.[/COLOR]
For i = iCountSep To 1 Step -1 'from last column to first column
sShort = Trim(Right(sRest, Len(sRest) - InStrRev(Trim(sRest), sSeparator)))
If Right(sShort, 1) = sSeparator Then sShort = Left(sShort, Len(sShort) - 1)
If Left(sShort, 1) = "0" Then oRange.Offset(0, 1).EntireColumn.Cells.NumberFormat = "@"
[COLOR=#008000]' detects leading zeros[/COLOR]
oRange.Offset(0, 1) = Trim(sShort)
sRest = Trim(Left(sRest, Len(sRest) - Len(sShort)))
If Right(sRest, 1) = sSeparator Then sRest = Left(sRest, Len(sRest) - 1)
Next i
If Left(sRest, 1) = "0" Then oRange.Offset(0, i).EntireColumn.Cells.NumberFormat = "@"
oRange.Offset(0, i) = CStr(sRest)
End With
Next n
oRange.CurrentRegion.EntireColumn.AutoFit
oRange.Cells(1, 1).Select
End Sub
Thank you.