Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 1
Smile 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Error Handler displaying message when no error Code Numpty Excel Programming 5 September 28th 09 07:25 PM
replace VBA run-time error message with custom message BEEJAY Excel Programming 13 July 14th 06 03:59 PM
error message: compile error, argument not optional Pierre via OfficeKB.com Excel Programming 3 September 5th 05 03:45 PM
changing the message in an error message The Villages DA Excel Worksheet Functions 2 February 18th 05 06:30 PM
How do I get rid of "Compile error in hidden module" error message David Excel Discussion (Misc queries) 4 January 22nd 05 12:39 AM


All times are GMT +1. The time now is 06:37 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"