Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
intersect function problem
I am trying to get a couple of macros to run when a cell value changes. I can
get this to work but I have a problem caused by the selection of multiple cells. In the following code the macro delete_sheet works fine when a single cell value in the keycells range is changed to 'No comment', however, the add_sheet macro has part of its code which selects multiple cells in other ranges and edits them. As soon as the add_sheet macro gets to this point I get the error 'Run time error 13 - type mismatch'. Same error occurs if multiple cells are selected manually and I try to edit them. The following code is embedded in the worksheet while the other 2 macros are modules. Any advice greatly appreciated. Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range Set KeyCells = Range("H11:H65536") ', Range("H11").End(xlDown)) If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing And Range(Target.Address) = "No Comment" Then delete_sheet ElseIf Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing And Range(Target.Address) = "See Comments Provided" Then add_sheet End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
intersect function problem
Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range if Target.count = 1 then exit sub Set KeyCells = Range("H11:H65536") ', Range("H11").End(xlDown)) If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing And Range(Target.Address) = "No Comment" Then delete_sheet ElseIf Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing And Range(Target.Address) = "See Comments Provided" Then add_sheet End If End Sub Using Range(Target.Address) is like saying Range(Range("A1").Address) to refer to cell A1. In otherwords, just use Target since it is a reference to a range. Same as if you did set Target = Range("A1") -- Regards, Tom Ogilvy "pumpbhoy" wrote in message ... I am trying to get a couple of macros to run when a cell value changes. I can get this to work but I have a problem caused by the selection of multiple cells. In the following code the macro delete_sheet works fine when a single cell value in the keycells range is changed to 'No comment', however, the add_sheet macro has part of its code which selects multiple cells in other ranges and edits them. As soon as the add_sheet macro gets to this point I get the error 'Run time error 13 - type mismatch'. Same error occurs if multiple cells are selected manually and I try to edit them. The following code is embedded in the worksheet while the other 2 macros are modules. Any advice greatly appreciated. Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range Set KeyCells = Range("H11:H65536") ', Range("H11").End(xlDown)) If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing And Range(Target.Address) = "No Comment" Then delete_sheet ElseIf Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing And Range(Target.Address) = "See Comments Provided" Then add_sheet End If End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
intersect function problem
Thanks for this. I have got it working ok now.
"Tom Ogilvy" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range if Target.count = 1 then exit sub Set KeyCells = Range("H11:H65536") ', Range("H11").End(xlDown)) If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing And Range(Target.Address) = "No Comment" Then delete_sheet ElseIf Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing And Range(Target.Address) = "See Comments Provided" Then add_sheet End If End Sub Using Range(Target.Address) is like saying Range(Range("A1").Address) to refer to cell A1. In otherwords, just use Target since it is a reference to a range. Same as if you did set Target = Range("A1") -- Regards, Tom Ogilvy "pumpbhoy" wrote in message ... I am trying to get a couple of macros to run when a cell value changes. I can get this to work but I have a problem caused by the selection of multiple cells. In the following code the macro delete_sheet works fine when a single cell value in the keycells range is changed to 'No comment', however, the add_sheet macro has part of its code which selects multiple cells in other ranges and edits them. As soon as the add_sheet macro gets to this point I get the error 'Run time error 13 - type mismatch'. Same error occurs if multiple cells are selected manually and I try to edit them. The following code is embedded in the worksheet while the other 2 macros are modules. Any advice greatly appreciated. Private Sub Worksheet_Change(ByVal Target As Range) Dim KeyCells As Range Set KeyCells = Range("H11:H65536") ', Range("H11").End(xlDown)) If Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing And Range(Target.Address) = "No Comment" Then delete_sheet ElseIf Not Application.Intersect(KeyCells, Range(Target.Address)) _ Is Nothing And Range(Target.Address) = "See Comments Provided" Then add_sheet End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to get the intersect cell data out using vba or function | Excel Worksheet Functions | |||
Opposite of Intersect function - an example | Excel Programming | |||
Syntax problem with" intersect" | Excel Programming | |||
Intersect Formula??? | Excel Programming | |||
Help with If Not Intersect | Excel Programming |