JacobMortensen
Board Regular
- Joined
- Feb 25, 2015
- Messages
- 85
Dear All.
In the code below I'm having problems with the second "On error GoTo" statement(UserCancelOnOutputSelection) - it doesn't work and that results in a runtime error: Division by zero.
Can someone help me?
In the code below I'm having problems with the second "On error GoTo" statement(UserCancelOnOutputSelection) - it doesn't work and that results in a runtime error: Division by zero.
Can someone help me?
Code:
Option Explicit
Sub SplitToMultipleColumns()
Dim TitleId As String: TitleId = "Split Columns"
Dim inputArray() As Variant
Dim inputRange As Range
Dim outputRange As Range
Dim NumberOfoutputRows As Integer
Dim NumberOfOutputColumns As Integer
Dim NumberOfCells As Integer
Dim i As Integer
Dim ArrayInputvalue As Variant
Dim iRow As Integer
Dim iCol As Integer
GoBackToSelectingRange:
On Error GoTo UserCancelOnRangeSelection
Set inputRange = Application.Selection
Set inputRange = Application.InputBox("Select the range you want to split:", TitleId, inputRange.Address, Type:=8)
On Error GoTo 0
GoBackToSelectingOutputRange:
On Error GoTo UserCancelOnOutputSelection
NumberOfoutputRows = Application.InputBox("Enter number of rows that you want to split into:", TitleId)
On Error GoTo 0
NumberOfOutputColumns = inputRange.Cells.Count / NumberOfoutputRows
On Error GoTo UserCancelOnOutputRange
Set outputRange = Application.InputBox("Out put to (single cell):", TitleId, Type:=8)
On Error GoTo 0
ReDim inputArray(1 To NumberOfoutputRows, 1 To NumberOfOutputColumns)
NumberOfCells = inputRange.Cells.Count - 1
For i = 0 To NumberOfCells
ArrayInputvalue = inputRange.Cells(i + 1)
iRow = i Mod NumberOfoutputRows
iCol = Int(i / NumberOfoutputRows)
inputArray(iRow + 1, iCol + 1) = ArrayInputvalue
Next i
outputRange.Resize(UBound(inputArray, 1), UBound(inputArray, 2)).Value = inputArray
Exit Sub
'************************
'Error handling section:
'************************
UserCancelOnRangeSelection: Exit Sub
UserCancelOnOutputSelection: Resume GoBackToSelectingRange
UserCancelOnOutputRange: Resume GoBackToSelectingOutputRange
'*************************************************************
End Sub