Subscript out of Range

[RyanH]

I am getting an error on Set removeRef line. The error state Subscript out
of Range, why is this?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

Cancel = True
On Error GoTo LineExit 'if Quote sheet is blank it ignores error
Target = ActiveCell
On Error GoTo 0

Dim myRef As Range
Dim removeRef As Range
Dim LastRow As Long
Dim FirstRow As Long
Dim SelectRow As Long


'procedure if "Remove" is double clicked
If Target.Value = "Remove" Then
Response = MsgBox("Are you sure you want to remove " &
Target.Offset(-3).Text & " from the quote sheet?", vbOKCancel, "Remove
Product")
If Response <> vbOK Then Exit Sub

Application.ScreenUpdating = False

Select Case Left(Target.Value, 2)
Case Is = "PF"
sh = "Plastic Faces"
Case Is = "EC"
sh = "Extruded Cabinets"
End Select

'finds reference number that is above the "Remove" that was clicked
Set removeRef =
Sheets(sh).Rows(1).Find(What:=Target.Offset(-3).Value, _
After:=Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

'clears the particular product data that is stored in the plastic faces
worksheet
Sheets("QUOTE").Unprotect Password:="AdTech"
Sheets(sh).Unprotect Password:="AdTech"

'MY CODE CONTINUES...................

Thanks,
Ryan

 

[JLGWhiz]

Unless your ActiveSheet happens to be either "Plastic Faces" or "Extruded
Cabinets" It won't find the value of "Target.Offset(-3)" since that was on
the ActiveSheet. What you need to do is set that value to a variable and
then use the variable in the find statement.

 

[Dave Peterson]

I'd change this portion:
After:=Cells(1), _
to
After:=Sheets(sh).Cells(1), _

Unqualified ranges in the worksheet module will refer to the sheet owning the
code--probably not what you wanted.

 

[RyanH]

Thanks for your responses. I am getting a Type Mismatch Error. Forgive me I
am new to VBA. I am still trying to learn the errors, because I get a lot of
them, lol.

My goal here is to double click on a Reference Number (Target) and my code
should extract the first to letters of the reference number. These first two
letters will tell the code what worksheet the reference number will be found.
At that point it will edit data or clear data. Here is the first portion of
my code, which still contains the error.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

Cancel = True
On Error GoTo LineExit 'if Quote sheet is blank it ignores error
Target = ActiveCell
On Error GoTo 0

Dim removeRef As Range
Dim sh As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim myRef As Range

'procedure if "Remove" is double clicked
If Target.Value = "Remove" Then
Response = MsgBox("Are you sure you want to remove " &
Target.Offset(-3).Text & " from the quote sheet?", vbOKCancel, "Remove
Product")
If Response <> vbOK Then Exit Sub

Application.ScreenUpdating = False

Select Case Left(Target.Value, 2)
Case Is = "PF"
sh = "Plastic Faces"
Case Is = "EC"
sh = "Extruded Cabinets"
End Select

'finds reference number that is above the "Remove" that was clicked
Set removeRef =
Sheets(sh).Rows(1).Find(What:=Target.Offset(-3).Value, _
After:=Sheets(sh).Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

'clears the particular product data that is stored in the plastic faces
worksheet
Sheets("QUOTE").Unprotect Password:="AdTech"
Sheets(sh).Unprotect Password:="AdTech"

With Sheets(sh).Range(removeRef.Address)
.EntireColumn.ClearContents
.Offset(0, 2).EntireColumn.ClearContents
End With

'code continues from here........

Thanks again Ryan

 

[JLGWhiz]

Hi Ryan, I guess I wasn't very clear before. Here is a modification to your
code.

If Target.Value = "Remove" Then
Response = MsgBox("Are you sure you want to remove " &
Target.Offset(-3).Text & " from the quote sheet?", vbOKCancel, "Remove
Product")
If Response <> vbOK Then Exit Sub
myVar = Target.Offset(-3, 0).Value

Application.ScreenUpdating = False

Select Case Left(Target.Value, 2)
Case Is = "PF"
sh = "Plastic Faces"
Case Is = "EC"
sh = "Extruded Cabinets"
End Select

'finds reference number that is above the "Remove" that was clicked
Set removeRef =
Sheets(sh).Rows(1).Find(What:=myVar, _
After:=Sheets(sh).Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

Notice the myVar line. It sets the offset range value to a variable, which
is then used in the Find statement. You need to do this since you are using
a case statement to determine which sheet you are going to search, and the
selected sheet probably will not be the one with the offset range in it. The
myVar variable will be the same no matter which sheet is selected, whereas
the Target.Offset(-3) reference only applied to the original ActiveSheet
value. When you go to a new sheet to Find a value of Target.Offset(-3), it
don't know where the Target is. Hope this helps.

 

[RyanH]

Thanks again for getting back with me. Your explanation made sense, but I am
still getting a Type Mismatch Error. Does the error have something to do
with the sh variable? Should I Dim myVar as String and Dim sh as Worksheet?

Thanks
Ryan

 

[Dave Peterson]

You sure about this statement.

Try adding
msgbox target.offset(-3).value
before and after the find.

I see no reason why that portion of the code is the problem.

Notice the myVar line. It sets the offset range value to a variable, which
is then used in the Find statement. You need to do this since you are using
a case statement to determine which sheet you are going to search, and the
selected sheet probably will not be the one with the offset range in it. The
myVar variable will be the same no matter which sheet is selected, whereas
the Target.Offset(-3) reference only applied to the original ActiveSheet
value. When you go to a new sheet to Find a value of Target.Offset(-3), it
don't know where the Target is. Hope this helps.

:

<<snipped>>

 

[Dave Peterson]

Time to post your current code and the indicate the line that causes the error.

Maybe it's a problem when you set your sh variable.

I'd still add code to avoid case differences (PF <> pF <> Pf <> pf) (same with
the Remove check, but I'm repeating myself).

And I'd add a check like:
....
case else
sh = ""
End Select

if sh = "" then
msgbox "not a valid pfx"
else
'do the find
end if

 

[RyanH]

The current code is posted below. I just posted the beginning portion of the
code because the entire code is pretty long. The Type Mismatch Error is
highlighted at the Set removeRef variable. This code is placed in Sheet1.

The first two letters will always be capitalized. When I initalize the
UserForm it adds the Two Letters to the time stamp. For example, when the
Plastic Faces UserForm is intialized the Reference Number is "PF" & Date &
Time.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

Cancel = True
On Error GoTo LineExit 'if Quote sheet is blank it ignores error
Target = ActiveCell
On Error GoTo 0

Dim removeRef As Range
Dim sh As Worksheet
'Dim myVar As String
Dim FirstRow As Long
Dim LastRow As Long
Dim myRef As Range

'procedure if "Remove" is double clicked
If Target.Value = "Remove" Then
Response = MsgBox("Are you sure you want to remove " &
Target.Offset(-3).Text & " from the quote sheet?", vbOKCancel, "Remove
Product")
If Response <> vbOK Then Exit Sub

Application.ScreenUpdating = False

'uses the first two letters of the reference number and tells what worksheet
to find it in
Select Case Left$(Target.Value, 2)
Case Is = "PF"
sh = "Plastic Faces"
Case Is = "EC"
sh = "Extruded Cabinets"
End Select

'reference number to find
myVar = Target.Offset(-3, 0).Value
MsgBox (myVar)

'finds reference number that is above the "Remove" that was clicked
ERROR==> Set removeRef = Sheets(sh).Rows(1).Find(What:=myVar, _
After:=Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

'clears the particular product data that is stored in the plastic faces
worksheet
Sheets("QUOTE").Unprotect Password:="AdTech"
Sheets(sh).Unprotect Password:="AdTech"

With Sheets(sh).Range(removeRef.Address)
.EntireColumn.ClearContents
.Offset(0, 2).EntireColumn.ClearContents
End With

'removes data that was requested to be removed from the quote sheet

FirstRow = Range("B" & (ActiveCell.Row - 1)).End(xlUp).Row
LastRow = Range("E" & (ActiveCell.Row + 1)).End(xlDown).Row
LastRow = LastRow - 1
If LastRow = (Rows.Count - 1) Then
LastRow = Range("C" & Rows.Count).End(xlUp).Row
End If
Rows(FirstRow & ":" & LastRow).Delete

'CODE CONTINUES.............

Thanks,Ryan

 

[Dave Peterson]

I don't see where you tried any of my previous suggestions.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)

Cancel = True
On Error GoTo LineExit 'if Quote sheet is blank it ignores error
Target = ActiveCell
On Error GoTo 0

Dim removeRef As Range
Dim sh As Worksheet
'Dim myVar As String
Dim FirstRow As Long
Dim LastRow As Long
Dim myRef As Range

'procedure if "Remove" is double clicked
If lcase(Target.Value) = lcase("Remove") Then
Response = MsgBox("Are you sure you want to remove " & _
Target.Offset(-3).Text & " from the quote sheet?", vbOKCancel, _
"Remove Product")
If Response <> vbOK Then Exit Sub

Application.ScreenUpdating = False

'uses the first two letters of the reference number and tells what worksheet
to find it in
Select Case ucase(Left$(Target.Value, 2))
Case Is = "PF"
sh = "Plastic Faces"
Case Is = "EC"
sh = "Extruded Cabinets"
case else
sh = ""
End Select

if sh = "" then
msgbox "not quite always!"
exit sub '????
else
if Target.row < 3 then
msgbox "too high to go up 3 rows
exit sub
else
'finds reference number that is above
'the "Remove" that was clicked
Set removeRef _
= Sheets(sh).Rows(1).Find(What:=Target.Offset(-3, 0).Value, _
After:=sheets(sh).Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
end if
end if

if removeref is nothing then
msgbox "not found"
else
msgbox "found at: " & removeref.address(external:=true)
end if

....

 

[Dave Peterson]

ps. I'd also check to see where the user double clicked:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)

'probably not necessary with doubleclicking, but it doesn't hurt
if target.cells.count > 1 then exit sub

if target.row < 3 then exit sub

'only check columns C:F
if intersect(target, me.range("c:f")) is nothing then
exit sub
end if

Cancel = True

'I don't understand what you're doing here, either.
On Error GoTo LineExit
'if Quote sheet is blank it ignores error Target = ActiveCell
On Error GoTo 0

.....

 

[RyanH]

I am a moron! Ha ha, I needed to add Left$(Target.Offset(-3, 0).Value, 2) as
my test expression in the Select Case. I forgot the Offset.

Thank you for all your help!!

ps. I'd also check to see where the user double clicked:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)

'probably not necessary with doubleclicking, but it doesn't hurt
if target.cells.count > 1 then exit sub

if target.row < 3 then exit sub

'only check columns C:F
if intersect(target, me.range("c:f")) is nothing then
exit sub
end if

Cancel = True

'I don't understand what you're doing here, either.
On Error GoTo LineExit
'if Quote sheet is blank it ignores error Target = ActiveCell
On Error GoTo 0

.....

 

[Dave Peterson]

I'd add some more checks.

I am a moron! Ha ha, I needed to add Left$(Target.Offset(-3, 0).Value, 2) as
my test expression in the Select Case. I forgot the Offset.

Thank you for all your help!!