In the following code, I am getting a next without for error after dropping in the 'If IsNumeric(strSplitter(x)) Then' line
Any ideas as to why? The 'Next x' has a corresponding 'For x'
Any ideas as to why? The 'Next x' has a corresponding 'For x'
Code:
Sub ExpandAIM()
Dim wb As Workbook
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim lngRowFrom As Long
Dim lngRowTo As Long
Dim x As Integer 'Loop through the array
Dim strSplitter() As String
Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet
Set ws2 = Sheet2
lngRowTo = 2
For lngRowFrom = 2 To ws.Range("A65000").End(xlUp).Row
'The "to" part is finding the last row, so you don't have to specify
If InStr(ws.Cells(lngRowFrom, 1).Value, ",") Then
strSplitter = Split(ws.Cells(lngRowFrom, 1).Value, ",")
For x = LBound(strSplitter) To UBound(strSplitter)
[blue]If IsNumeric(strSplitter(x)) Then[/blue]
ws2.Cells(lngRowTo, 1).Value = strSplitter(x)
Do While ws2.Cells(lngRowTo, 1).Value = vbNullString
Loop
ws2.Cells(lngRowTo, 1).Value = strSplitter(x)
ws2.Cells(lngRowTo, 2).Value = ws.Cells(lngRowFrom, 2).Value
ws2.Cells(lngRowTo, 3).Value = ws.Cells(lngRowFrom, 3).Value
ws2.Cells(lngRowTo, 4).Value = ws.Cells(lngRowFrom, 4).Value
ws2.Cells(lngRowTo, 5).Value = ws.Cells(lngRowFrom, 5).Value
lngRowTo = lngRowTo + 1
Next x
Else
Do While ws2.Cells(lngRowTo, 1).Value = vbNullString
ws2.Cells(lngRowTo, 1).Value = ws.Cells(lngRowFrom, 1).Value
ws2.Cells(lngRowTo, 2).Value = ws.Cells(lngRowFrom, 2).Value
ws2.Cells(lngRowTo, 3).Value = ws.Cells(lngRowFrom, 3).Value
ws2.Cells(lngRowTo, 4).Value = ws.Cells(lngRowFrom, 4).Value
ws2.Cells(lngRowTo, 5).Value = ws.Cells(lngRowFrom, 5).Value
Loop
lngRowTo = lngRowTo + 1
End If
Next lngRowFrom
End Sub