| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple If Statements or Macro's in Worksheet:View Code
Hello,
I am trying to sort two different areas in one worksheet. I am trying to use the following code but it only works for the first area not the second. Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Range("h3:n500"), Target) Is Nothing Then Exit Sub Application.EnableEvents = False Range("h3:n500").Sort Key1:=Range("h3") Application.EnableEvents = True If Intersect(Range("p3:t500"), Target) Is Nothing Then Exit Sub Application.EnableEvents = False Range("p3:t500").Sort Key1:=Range("p3") Application.EnableEvents = True End Sub |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple If Statements or Macro's in Worksheet:View Code
If the Target doesn't overlap both ranges, your code exits on the first
test. You'll have to modify it something like this: If Not Intersect(Range("h3:n500"), Target) Is Nothing Then ... Do the sort on the first range. End if If Not Intersect(Range("p3:t500"), Target) Is Nothing Then ... Do the sort on the second range. End if If they are mutually exclusive, you could change it to: If Not Intersect(Range("h3:n500"), Target) Is Nothing Then ... First sort Elseif Not Intersect(Range("p3:t500"),Target) Is Nothing Then ... Second sort End if Scott Ben Dummar wrote: Hello, I am trying to sort two different areas in one worksheet. I am trying to use the following code but it only works for the first area not the second. Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Range("h3:n500"), Target) Is Nothing Then Exit Sub Application.EnableEvents = False Range("h3:n500").Sort Key1:=Range("h3") Application.EnableEvents = True If Intersect(Range("p3:t500"), Target) Is Nothing Then Exit Sub Application.EnableEvents = False Range("p3:t500").Sort Key1:=Range("p3") Application.EnableEvents = True End Sub |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple If Statements or Macro's in Worksheet:View Code
This may work better...
Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Range("h3:n500"), Target) Is Nothing Then Exit Sub Application.EnableEvents = False Range("h3:n500").Sort Key1:=Range("h3"), Order1:=xlAscending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Application.EnableEvents = True If Intersect(Range("p3:t500"), Target) Is Nothing Then Exit Sub Application.EnableEvents = False Range("p3:t500").Sort Key1:=Range("p3"), Order1:=xlAscending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Application.EnableEvents = True End Sub Also, if you still want to sort by the second range even if the first range is missing, you'll want to create a second Sub to perform the second range sort, as it currently is ending the entire sub once the first set is blank. "Ben Dummar" wrote: Hello, I am trying to sort two different areas in one worksheet. I am trying to use the following code but it only works for the first area not the second. Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Range("h3:n500"), Target) Is Nothing Then Exit Sub Application.EnableEvents = False Range("h3:n500").Sort Key1:=Range("h3") Application.EnableEvents = True If Intersect(Range("p3:t500"), Target) Is Nothing Then Exit Sub Application.EnableEvents = False Range("p3:t500").Sort Key1:=Range("p3") Application.EnableEvents = True End Sub |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple If Statements or Macro's in Worksheet:View Code
Sean,
Thanks for the response. I tried the code but it stops the editor. I think one thing I need to clarify is that I want both list's to operate independantly of each other. If a name is added to one list it re-sorts that list but doesn't affect the other list and vice-versa. Ben "Sean Timmons" wrote: This may work better... Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Range("h3:n500"), Target) Is Nothing Then Exit Sub Application.EnableEvents = False Range("h3:n500").Sort Key1:=Range("h3"), Order1:=xlAscending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Application.EnableEvents = True If Intersect(Range("p3:t500"), Target) Is Nothing Then Exit Sub Application.EnableEvents = False Range("p3:t500").Sort Key1:=Range("p3"), Order1:=xlAscending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Application.EnableEvents = True End Sub Also, if you still want to sort by the second range even if the first range is missing, you'll want to create a second Sub to perform the second range sort, as it currently is ending the entire sub once the first set is blank. "Ben Dummar" wrote: Hello, I am trying to sort two different areas in one worksheet. I am trying to use the following code but it only works for the first area not the second. Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Range("h3:n500"), Target) Is Nothing Then Exit Sub Application.EnableEvents = False Range("h3:n500").Sort Key1:=Range("h3") Application.EnableEvents = True If Intersect(Range("p3:t500"), Target) Is Nothing Then Exit Sub Application.EnableEvents = False Range("p3:t500").Sort Key1:=Range("p3") Application.EnableEvents = True End Sub |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple If Statements or Macro's in Worksheet:View Code
Then put each block in an:
If Not Intersect(...) Is Nothing Then ... End if Scott Ben Dummar wrote: Sean, Thanks for the response. I tried the code but it stops the editor. I think one thing I need to clarify is that I want both list's to operate independantly of each other. If a name is added to one list it re-sorts that list but doesn't affect the other list and vice-versa. Ben "Sean Timmons" wrote: This may work better... Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Range("h3:n500"), Target) Is Nothing Then Exit Sub Application.EnableEvents = False Range("h3:n500").Sort Key1:=Range("h3"), Order1:=xlAscending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Application.EnableEvents = True If Intersect(Range("p3:t500"), Target) Is Nothing Then Exit Sub Application.EnableEvents = False Range("p3:t500").Sort Key1:=Range("p3"), Order1:=xlAscending, Header:=xlGuess, _OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Application.EnableEvents = True End Sub Also, if you still want to sort by the second range even if the first range is missing, you'll want to create a second Sub to perform the second range sort, as it currently is ending the entire sub once the first set is blank. "Ben Dummar" wrote: Hello, I am trying to sort two different areas in one worksheet. I am trying to use the following code but it only works for the first area not the second. Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Range("h3:n500"), Target) Is Nothing Then Exit Sub Application.EnableEvents = False Range("h3:n500").Sort Key1:=Range("h3") Application.EnableEvents = True If Intersect(Range("p3:t500"), Target) Is Nothing Then Exit Sub Application.EnableEvents = False Range("p3:t500").Sort Key1:=Range("p3") Application.EnableEvents = True End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macros - copying macros from one computer to another | Excel Discussion (Misc queries) | |||
| copy and use vba code | Excel Discussion (Misc queries) | |||
| macro or code to open multiple workbooks | Excel Discussion (Misc queries) | |||
| How to: Multiple "if" statements? | New Users to Excel | |||
| Make Change Case in Excel a format rather than formula | Excel Worksheet Functions | |||
Linear Mode
