| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
More Efficient If Statement
I have the following code as part of a macro. Is there a more efficient (i.e.
less code) way of writing this IF statement? Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Address = "$G$9" Or Target.Address = "$G$10" Or _ Target.Address = "$G$11" Or Target.Address = "$G$12" Or _ Target.Address = "$G$13" Or Target.Address = "$G$17" Or _ Target.Address = "$G$18" Or Target.Address = "$G$19" Or _ Target.Address = "$G$20" Or Target.Address = "$G$21" Or _ Target.Address = "$G$25" Or Target.Address = "$G$26" Or _ Target.Address = "$G$27" Or Target.Address = "$G$28" Or _ Target.Address = "$G$29" Then ........................ TIA. -- Ken Hudson |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More Efficient If Statement
Try it this way....
If Intersect(Target, Range("G9:G13,G17:G21,G25:G29")) Then Rick "Ken Hudson" wrote in message ... I have the following code as part of a macro. Is there a more efficient (i.e. less code) way of writing this IF statement? Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Address = "$G$9" Or Target.Address = "$G$10" Or _ Target.Address = "$G$11" Or Target.Address = "$G$12" Or _ Target.Address = "$G$13" Or Target.Address = "$G$17" Or _ Target.Address = "$G$18" Or Target.Address = "$G$19" Or _ Target.Address = "$G$20" Or Target.Address = "$G$21" Or _ Target.Address = "$G$25" Or Target.Address = "$G$26" Or _ Target.Address = "$G$27" Or Target.Address = "$G$28" Or _ Target.Address = "$G$29" Then ....................... TIA. -- Ken Hudson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More Efficient If Statement
Thanks Rick!
I knew there had to be a better way. If I could just graduate from "plain vanilla" programming to "chocolate" or "strawberry", I'd be dangerous. -- Ken Hudson "Rick Rothstein (MVP - VB)" wrote: Try it this way.... If Intersect(Target, Range("G9:G13,G17:G21,G25:G29")) Then Rick "Ken Hudson" wrote in message ... I have the following code as part of a macro. Is there a more efficient (i.e. less code) way of writing this IF statement? Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Address = "$G$9" Or Target.Address = "$G$10" Or _ Target.Address = "$G$11" Or Target.Address = "$G$12" Or _ Target.Address = "$G$13" Or Target.Address = "$G$17" Or _ Target.Address = "$G$18" Or Target.Address = "$G$19" Or _ Target.Address = "$G$20" Or Target.Address = "$G$21" Or _ Target.Address = "$G$25" Or Target.Address = "$G$26" Or _ Target.Address = "$G$27" Or Target.Address = "$G$28" Or _ Target.Address = "$G$29" Then ....................... TIA. -- Ken Hudson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More Efficient If Statement
When I tested this code, I received the following error when I entered
anything into a cell in the worksheet that is not part of the IF statement: Run-time error 91. Object variable or With block variable not set. What am I missing? -- Ken Hudson "Rick Rothstein (MVP - VB)" wrote: Try it this way.... If Intersect(Target, Range("G9:G13,G17:G21,G25:G29")) Then Rick "Ken Hudson" wrote in message ... I have the following code as part of a macro. Is there a more efficient (i.e. less code) way of writing this IF statement? Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Address = "$G$9" Or Target.Address = "$G$10" Or _ Target.Address = "$G$11" Or Target.Address = "$G$12" Or _ Target.Address = "$G$13" Or Target.Address = "$G$17" Or _ Target.Address = "$G$18" Or Target.Address = "$G$19" Or _ Target.Address = "$G$20" Or Target.Address = "$G$21" Or _ Target.Address = "$G$25" Or Target.Address = "$G$26" Or _ Target.Address = "$G$27" Or Target.Address = "$G$28" Or _ Target.Address = "$G$29" Then ....................... TIA. -- Ken Hudson |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More Efficient If Statement
The only thing missing was my full attention... I'm sorry... I was on a
mini-vacation to visit my son this weekend and only popped onto these newsgroups a couple of times and didn't spend as much time as I should have in answering the few questions that I did. What I posted was only partially correct, but I left out the most important part... try it this way If Not Intersect(Target, Range("G9:G13,G17:G21,G25:G29")) Is Nothing Then Rick "Ken Hudson" wrote in message ... When I tested this code, I received the following error when I entered anything into a cell in the worksheet that is not part of the IF statement: Run-time error 91. Object variable or With block variable not set. What am I missing? -- Ken Hudson "Rick Rothstein (MVP - VB)" wrote: Try it this way.... If Intersect(Target, Range("G9:G13,G17:G21,G25:G29")) Then Rick "Ken Hudson" wrote in message ... I have the following code as part of a macro. Is there a more efficient (i.e. less code) way of writing this IF statement? Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Address = "$G$9" Or Target.Address = "$G$10" Or _ Target.Address = "$G$11" Or Target.Address = "$G$12" Or _ Target.Address = "$G$13" Or Target.Address = "$G$17" Or _ Target.Address = "$G$18" Or Target.Address = "$G$19" Or _ Target.Address = "$G$20" Or Target.Address = "$G$21" Or _ Target.Address = "$G$25" Or Target.Address = "$G$26" Or _ Target.Address = "$G$27" Or Target.Address = "$G$28" Or _ Target.Address = "$G$29" Then ....................... TIA. -- Ken Hudson |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
More Efficient If Statement
Thanks again.
I figured there was something else missing in the statement and tried: If Not IsEmpty(Target, Range("G9:G13,G17:G21,G25:G29")) Then but that didn't do it. -- Ken Hudson "Rick Rothstein (MVP - VB)" wrote: The only thing missing was my full attention... I'm sorry... I was on a mini-vacation to visit my son this weekend and only popped onto these newsgroups a couple of times and didn't spend as much time as I should have in answering the few questions that I did. What I posted was only partially correct, but I left out the most important part... try it this way If Not Intersect(Target, Range("G9:G13,G17:G21,G25:G29")) Is Nothing Then Rick "Ken Hudson" wrote in message ... When I tested this code, I received the following error when I entered anything into a cell in the worksheet that is not part of the IF statement: Run-time error 91. Object variable or With block variable not set. What am I missing? -- Ken Hudson "Rick Rothstein (MVP - VB)" wrote: Try it this way.... If Intersect(Target, Range("G9:G13,G17:G21,G25:G29")) Then Rick "Ken Hudson" wrote in message ... I have the following code as part of a macro. Is there a more efficient (i.e. less code) way of writing this IF statement? Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False If Target.Address = "$G$9" Or Target.Address = "$G$10" Or _ Target.Address = "$G$11" Or Target.Address = "$G$12" Or _ Target.Address = "$G$13" Or Target.Address = "$G$17" Or _ Target.Address = "$G$18" Or Target.Address = "$G$19" Or _ Target.Address = "$G$20" Or Target.Address = "$G$21" Or _ Target.Address = "$G$25" Or Target.Address = "$G$26" Or _ Target.Address = "$G$27" Or Target.Address = "$G$28" Or _ Target.Address = "$G$29" Then ....................... TIA. -- Ken Hudson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| More Efficient IF | Excel Programming | |||
| More Efficient IF | Excel Programming | |||
| Is there a more efficient way to do this? | Excel Programming | |||
| More efficient way? | Excel Programming | |||
| Q: Is this vlookup statement efficient? | Excel Programming | |||
Linear Mode
