Hello,
I have created a VBA to open a userform and enter data, but I keep getting this error pop up "Error 9 (Subscript out of range) in procedure Sortit of Module Assorted", but the information still imports to the cells and sorts the spreadsheet. How do I fix this?
Userform VBA:
Module (Macro Sortit)
I have created a VBA to open a userform and enter data, but I keep getting this error pop up "Error 9 (Subscript out of range) in procedure Sortit of Module Assorted", but the information still imports to the cells and sorts the spreadsheet. How do I fix this?
Userform VBA:
Code:
Private Sub cmdAddProduct_Click()
Dim X As Integer
Dim nextrow As Range
On Error GoTo cmdAddProduct_Click_Error
Set nextrow = Sheet5.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
For X = 1 To 6
If Me.Controls("Product" & X).Value = "" Then
MsgBox "Missing data"
Exit Sub
End If
Next
If WorksheetFunction.CountIf(Sheet5.Range("E:E"), Me.Product4.Value) > 0 Then
MsgBox "This product code already exists"
Exit Sub
End If
If WorksheetFunction.CountIf(Sheet5.Range("L:L"), Me.Product2.Value) = 0 Then
Select Case MsgBox("This is a new category. It will now be added to the category list.." _
& vbCrLf & "Are you sure that you want to proceed?" _
, vbYesNo Or vbQuestion Or vbDefaultButton1, "Please check this first")
Case vbYes
Case vbNo
Exit Sub
End Select
Sheet5.Cells(Rows.Count, 12).End(xlUp).Offset(1, 0) = Me.Product2.Value
End If
For X = 1 To 6
nextrow = Me.Controls("Product" & X).Value
Set nextrow = nextrow.Offset(0, 1)
Next
'clear
For X = 1 To 6
Me.Controls("Product" & X).Value = ""
Next
Sortit
On Error GoTo 0
Exit Sub
cmdAddProduct_Click_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdAddProduct_Click of Form frmNewProduct"
End Sub
Module (Macro Sortit)
Code:
Sub Sortit()
On Error GoTo Sortit_Error
Application.ScreenUpdating = False
returnto = ActiveSheet.Name
Sheet5.Activate
With Sheet5
Sheet5.Range("B6:G10000").Sort Key1:=Range("C5"), Order1:=xlAscending, Header:=xlGuess
End With
ThisWorkbook.Sheets(returnto).Select
On Error GoTo 0
Exit Sub
Sortit_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Sortit of Module Assorted"
End Sub