Jump to content




Recent Status Updates

  • Photo
      19 Sep
    Chall

    I love it when you go to write a help thread, then while writing, you reach an enlightenment, and figure it out yourself.

    Show comments (3)
  • Photo
      15 Sep
    Error

    Programming is something that I enjoy and want to make a career out of. But, I usually tend to start things and not finish them. Any advice on how I can finish what I start?

    Show comments (4)
View All Updates

Developed by Kemal Taskin
Photo
- - - - -

If multiple conditions (Excel Macro)


  • Please log in to reply
3 replies to this topic

#1 fragilespark

fragilespark

    CC Lurker

  • Just Joined
  • Pip
  • 2 posts

Posted 12 January 2011 - 11:31 AM

I'm editing an Excel 2003 Macro created by someone else, and learning along the way. There is data from another report that is being imported and we want to split it into different sheets, to correspond to sales areas, depending on account number. This is done by deleting unnecessary rows. For example

Account, name, ordernumber
410027, ABC, 123
420001, XYZ, 789

where the first account number is in one sales area and the second is in another. It is not possible to add it to the original report as that imports from the system and doesn't have this data.

Some areas are easy because the accounts are in a specific range, which works like this:

If Account < 420000 Or Account > 489999 Then

(then delete selected rows etc) which returns info on account 420001 onwards. Perfect. However the area I'm working on also has a few isolated accounts which I have managed to bring up with the following:

If Account <> 400000 And Account <> 400001 And Account <> 400003 And Account <> 400005 And Account <> 400015 Then

Which deletes everything but those accounts. My question is, how do I combine the two so it brings up anything in the range plus the individual accounts? I have tried both

If Account < 420000 Or Account > 489999 And Account <> 400000 And Account <> 400001 And Account <> 400003 And Account <> 400005 And Account <> 400015 And Account Then

and

If Account < 420000 Or Account > 489999 Or Account <> 400000 And Account <> 400001 And Account <> 400003 And Account <> 400005 And Account <> 400015 And Account Then

but as it deletes everything I am wondering if this is not how it is done and hoping someone can point me in the right direction please. Thank you!

(If I can't make it work like this then I will have to propose instead of deleting rows we can inverse it and select the ones we want, but that will mean changing a lot more code)
  • 0

#2 WingedPanther

WingedPanther

    A spammer's worst nightmare

  • Moderator
  • 17,074 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 12 January 2011 - 12:52 PM

You should add parenthesis to group the OR's and AND's to get the effect you want. Otherwise, you can get that type of unexpected results. In this case, wrap the block of AND statements in parenthesis.
  • 1

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#3 fragilespark

fragilespark

    CC Lurker

  • Just Joined
  • Pip
  • 2 posts

Posted 13 January 2011 - 12:46 AM

Many thanks for your quick answer. I have tried the following:

If Account < 420000 Or Account > 489999 And (Account <> 400000 And Account <> 400001 And Account <> 400003 And Account <> 400005 And Account <> 400009 And Account <> 410000 And Account <> 410005 And Account <> 410007 And Account <> 410019 And Account <> 410028) Then
     Rows(iRowData).Select
     Selection.Delete Shift:=xlUp
     End If

However, it still brings up only account 420001 and not, for example 400005, which I can see information for on the original sheet. Have I put the parenthesis in the wrong place? Thank you

EDIT: I spoke too soon! Added Parentheses to the range as well and it worked.
  • 0

#4 WingedPanther

WingedPanther

    A spammer's worst nightmare

  • Moderator
  • 17,074 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 13 January 2011 - 04:19 AM

I'm glad to hear it :)
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/