Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 9???
I’m working on this piece of code that will use Col. “A” as a link t extract X row (Sheet3) and copy the info to Sheet1. It works fine up t the point of Copy after that it returns a “Run Time Error 9” / Subscrip out of Range. Any ideas how to correct? Any help is appreciated! ================================================== == Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cance As Boolean) Cancel = True Dim IntersectRange As Range Set WatchRange = Range("A10:A5000") Set IntersectRange = Intersect(Target, WatchRange) On Error GoTo 0 Application.EnableEvents = False With ActiveWorkbook.Worksheets("Sheet3") If IntersectRange Is Nothing Then MsgBox ("NO SELECTION MADE") Else Target = "B" & ActiveCell.Row & ":V" & ActiveCell.Row End If End With Range(Target).Select Selection.Copy Sheets("Sheet1").Range ("AP12") ActiveSheet.Paste Application.CutCopyMode = False Application.EnableEvents = True End Su -- Fabl ----------------------------------------------------------------------- Fable's Profile: http://www.excelforum.com/member.php...nfo&userid=218 View this thread: http://www.excelforum.com/showthread.php?threadid=50147 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 9???
Hi,
Try this: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True Dim IntersectRange As Range Set WatchRange = Range("A10:A5000") Set IntersectRange = Intersect(Target, WatchRange) On Error GoTo 0 Application.EnableEvents = False With ActiveWorkbook.Worksheets("Sheet3") If IntersectRange Is Nothing Then MsgBox ("NO SELECTION MADE") Else .Range("B" & ActiveCell.Row & ":V" & ActiveCell.Row).Copy Sheets("Sheet1").Range("AP12") End If End With Application.CutCopyMode = False Application.EnableEvents = True End Sub "Fable" wrote: Im working on this piece of code that will use Col. €œA€ as a link to extract X row (Sheet3) and copy the info to Sheet1. It works fine up to the point of Copy after that it returns a €œRun Time Error 9€ / Subscript out of Range. Any ideas how to correct? Any help is appreciated! ================================================== == Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True Dim IntersectRange As Range Set WatchRange = Range("A10:A5000") Set IntersectRange = Intersect(Target, WatchRange) On Error GoTo 0 Application.EnableEvents = False With ActiveWorkbook.Worksheets("Sheet3") If IntersectRange Is Nothing Then MsgBox ("NO SELECTION MADE") Else Target = "B" & ActiveCell.Row & ":V" & ActiveCell.Row End If End With Range(Target).Select Selection.Copy Sheets("Sheet1").Range ("AP12") ActiveSheet.Paste Application.CutCopyMode = False Application.EnableEvents = True End Sub -- Fable ------------------------------------------------------------------------ Fable's Profile: http://www.excelforum.com/member.php...fo&userid=2185 View this thread: http://www.excelforum.com/showthread...hreadid=501470 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 9???
Thanks Toppers! it still shows a run time error, however I worked out solution, basically paste the "result" on the same Sheet3 then copy export to sheet1 it an extra steps but it works -- Fabl ----------------------------------------------------------------------- Fable's Profile: http://www.excelforum.com/member.php...nfo&userid=218 View this thread: http://www.excelforum.com/showthread.php?threadid=50147 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Time Error 9???
FYI: it worked OK when I tested it.
The line Sheets("Sheet1").Range("AP12") should be on the same as the COPY line NOT a seperate line s shown in the posting: is this the problem? "Toppers" wrote: Hi, Try this: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True Dim IntersectRange As Range Set WatchRange = Range("A10:A5000") Set IntersectRange = Intersect(Target, WatchRange) On Error GoTo 0 Application.EnableEvents = False With ActiveWorkbook.Worksheets("Sheet3") If IntersectRange Is Nothing Then MsgBox ("NO SELECTION MADE") Else .Range("B" & ActiveCell.Row & ":V" & ActiveCell.Row).Copy Sheets("Sheet1").Range("AP12") End If End With Application.CutCopyMode = False Application.EnableEvents = True End Sub "Fable" wrote: Im working on this piece of code that will use Col. €œA€ as a link to extract X row (Sheet3) and copy the info to Sheet1. It works fine up to the point of Copy after that it returns a €œRun Time Error 9€ / Subscript out of Range. Any ideas how to correct? Any help is appreciated! ================================================== == Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Cancel = True Dim IntersectRange As Range Set WatchRange = Range("A10:A5000") Set IntersectRange = Intersect(Target, WatchRange) On Error GoTo 0 Application.EnableEvents = False With ActiveWorkbook.Worksheets("Sheet3") If IntersectRange Is Nothing Then MsgBox ("NO SELECTION MADE") Else Target = "B" & ActiveCell.Row & ":V" & ActiveCell.Row End If End With Range(Target).Select Selection.Copy Sheets("Sheet1").Range ("AP12") ActiveSheet.Paste Application.CutCopyMode = False Application.EnableEvents = True End Sub -- Fable ------------------------------------------------------------------------ Fable's Profile: http://www.excelforum.com/member.php...fo&userid=2185 View this thread: http://www.excelforum.com/showthread...hreadid=501470 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual Basic Error Run Time Error, Type Mismatch | Excel Discussion (Misc queries) | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming | |||
Run-time error '11' & Run-time error '1004' | Excel Programming |