| Home |
| Search |
| Today's Posts |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of Range
I'd add some more checks.
RyanH wrote: 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!! "Dave Peterson" wrote: 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 wrote: 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 .... RyanH wrote: 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" wrote: 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. JLGWhiz wrote: <<snipped 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" wrote: <<snipped -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| 9: Subscript out of range | Excel Discussion (Misc queries) | |||
| Subscript out of Range | Excel Programming | |||
| Subscript out of range | Excel Programming | |||
| Subscript out of range | Excel Programming | |||
| Subscript out of range | Excel Programming | |||
Threaded Mode
