ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   New Users to Excel (https://www.excelbanter.com/new-users-excel/)
-   -   VBA Error Message HELP!!!!! (https://www.excelbanter.com/new-users-excel/448755-vba-error-message-help.html)

Tansy

VBA Error Message HELP!!!!!
 
I'm coming up with the error message below, does anyone know what it means:

'An unexpected error has been detected
Description is:0, Subscript out of range
Module is: commandbutton_click

Code is:
Private Sub Create_Click()

On Error GoTo err_handler

Dim FindString As String
Dim Rng As Range
FindString = Orderform.ComboBox.Value
If Trim(FindString) < "" Then
With Sheets("Orders").Range("e:e")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
Application.GoTo Rng, True
End With
End If

Worksheets("Chaucer Order").Range("P22").Value = ""
Worksheets("Chaucer Order").Range("G12").Value = ""
Worksheets("Chaucer Order").Range("D30").Value = ""
Worksheets("Chaucer Order").Range("M30").Value = ""
Worksheets("Chaucer Order").Range("P20").Value = ""
Worksheets("Chaucer Order").Range("D30").Value = ""
Worksheets("Chaucer Order").Range("M30").Value = ""
Worksheets("Chaucer Order").Range("D31").Value = ""
Worksheets("Chaucer Order").Range("M31").Value = ""
Worksheets("Chaucer Order").Range("D32").Value = ""
Worksheets("Chaucer Order").Range("M32").Value = ""
Worksheets("Chaucer Order").Range("D33").Value = ""
Worksheets("Chaucer Order").Range("M33").Value = ""
Worksheets("Chaucer Order").Range("D34").Value = ""
Worksheets("Chaucer Order").Range("M34").Value = ""
Worksheets("Chaucer Order").Range("D35").Value = ""
Worksheets("Chaucer Order").Range("M35").Value = ""
Worksheets("Chaucer Order").Range("D36").Value = ""
Worksheets("Chaucer Order").Range("M36").Value = ""
Worksheets("Chaucer Order").Range("D37").Value = ""
Worksheets("Chaucer Order").Range("M37").Value = ""
Worksheets("Chaucer Order").Range("D38").Value = ""
Worksheets("Chaucer Order").Range("M38").Value = ""

Worksheets("Chaucer Order").Range("P22").Value = Selection.Value 'Order Number
Worksheets("Chaucer Order").Range("G12").Value = ActiveCell.Offset(0, 1).Value 'Cutomer Code
Worksheets("Chaucer Order").Range("D30").Value = ActiveCell.Offset(0, 3).Value 'Product Code
Worksheets("Chaucer Order").Range("M30").Value = ActiveCell.Offset(0, 6).Value 'Cases Ordered
Worksheets("Chaucer Order").Range("P20").Value = ActiveCell.Offset(0, -2).Value 'Delivery Date

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With


For i = 1 To 10
If FindString = ActiveCell.Offset(i, 0).Value Then
ActiveCell.Interior.Color = 5287936
Worksheets("Chaucer Order").Range("Order").Cells(i + 2, 1).Value = ActiveCell.Offset(i, 3).Value 'Product Code
Worksheets("Chaucer Order").Range("Order").Cells(i + 2, 10).Value = ActiveCell.Offset(i, 5).Value 'Cases Ordered
End If
Next
Unload Orderform
Orderform.Hide

Exit Sub

err_handler:
If Err.Number = 5 Then

MsgBox "Ensure the orders sheet is selected before running the 'create an order' macro or you have manually entered an incorrect Order Number, please try again"

Else

MsgBox _
"An unexpected error has been detected" & Chr(13) & _
"Description is: " & Err.Number & ", " & Err.Description & Chr(13) & _
"Module is: commandbutton_click" & Chr(13) & _
"Please note the above details before contacting support"
End If

End Sub

Any help appreciated!

xxx


All times are GMT +1. The time now is 08:10 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com