Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: How To Avoid Using Too Many OR and AND Functions In VBA

  1. #1
    Junior Member
    Join Date
    Nov 2017
    Posts
    21
    Rep Power
    0

    Lightbulb How To Avoid Using Too Many OR and AND Functions In VBA

    I have this code below,

    now if i have to modify this, add more conditions. for example more years to include for example instead of many OR and AND functions, i simply want to use an array for example lets say if i have more years to add then i simply use the {2002, 2011, 2012, 2013, 2014, 2015,} for year like this ShD.Cells(C.Row, intYearCol).Value = {2002, 2011, 2012, 2013, 2014, 2015,} and for months to exclude ShD.Cells(C.Row, intMonthCol).Value <> {111, XI, XII, XXII}

    thanks.

    Code:
    Sub macro3()
    intYearCol = Range("dataYear").Column
    intMonthCol = Range("dataMonth").Column
    intProductCol = Range("dataPRODUCT").Column
    intAmountCol = Range("dataAMOUNT").Column
    Set ShD = Sheets("Data")
    For Each C In Range(ShD.Range("a2"), ShD.Range("a" & Rows.Count).End(xlUp))
        If (ShD.Cells(C.Row, intYearCol).Value = 2011 Or ShD.Cells(C.Row, intYearCol).Value = 2012) _
                And ShD.Cells(C.Row, intMonthCol).Value <> 111 And _
                ShD.Cells(C.Row, intProductCol).Value Like "[5-7]*" Then
            mySum = mySum + ShD.Cells(C.Row, intAmountCol).Value
        End If
    Next
    Sheets("Main").Range("B3") = mySum
    End Sub

  2. #2

  3. #3
    Junior Member
    Join Date
    Nov 2017
    Posts
    21
    Rep Power
    0
    thanks very much for reply and willingness to help.

    i replied to the other thread. pivot table is not an option.

    thanks.

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    You could do something like this

    Code:
    If Instr(1,"|2001|2002|2003|2011|2012|2017", "|" & ShD.Cells(C.Row, intYearCol).Value & "|") >0 Then
    'Your other code
    End if
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member
    Join Date
    Nov 2017
    Posts
    21
    Rep Power
    0
    Quote Originally Posted by Excel Fox View Post
    You could do something like this

    Code:
    If Instr(1,"|2001|2002|2003|2011|2012|2017", "|" & ShD.Cells(C.Row, intYearCol).Value & "|") >0 Then
    'Your other code
    End if

    WOW!

    Admin you are amazing!

    it works.

    thanks so much

  6. #6
    Junior Member
    Join Date
    Nov 2017
    Posts
    21
    Rep Power
    0
    to make it work. i added one extra vertical bracket If Instr(1,"|2001|2002|2003|2011|2012|2017|", "|" & ShD.Cells(C.Row, intYearCol).Value & "|") >0 Then

    thanks alot


    if this is the one to include

    what could be the opposite of this.
    for example

    i would say exclude "|1998|1995|1992|1978|1983|1977|"

    is it something like i use Not or there is another trick for excluding?

    thanks Admin

  7. #7
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Instead of >0 use =0

    And yes, the last pipe character | was missed at my side. Good you noticed.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  8. #8
    Junior Member
    Join Date
    Nov 2017
    Posts
    21
    Rep Power
    0
    Thank you genius

  9. #9
    Junior Member
    Join Date
    Nov 2017
    Posts
    21
    Rep Power
    0
    dear Administrator,

    how can i change this code that instead of hard coded values of "|2001|2002|2003|2011|2012|2017|" i used cell reference

    for example. my 2001 will be in cell A2 and 2002 will be in A3 and 2003 will be in cell A4 and so on.

    i tried this and it did not work If Instr(1,"A2:A7", "|" & ShD.Cells(C.Row, intYearCol).Value & "|") >0

    screenshot A
    E.png

    also how to i change the code if the values are in one cell separated by comma like screenshot B

    for the screenshot below i used If Instr(1,"A1", "|" & ShD.Cells(C.Row, intYearCol).Value & "|") >0 and it did not work.
    2017-11-24 15_39_07-Book1 - Excel.png
    Last edited by flora; 11-24-2017 at 07:41 PM. Reason: attached screenshot.

  10. #10
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    If you are using it in one cell, instead of keeping it separated by a comma, why don't you keep it separated by pipe.

    And if you are using a range of cells, then try this

    Code:
        If WorksheetFunction.CountIf(Worksheets("Name").Range("A2:A7"), ShD.Cells(C.Row, intYearCol).Value) > 0 Then
            'Your code
        End If
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Color Functions In Excel
    By Admin in forum Download Center
    Replies: 2
    Last Post: 10-24-2013, 11:44 AM
  2. VBA Trick of the Week :: Avoid Loop for Range Calculations - Evaluate
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 06-18-2013, 11:42 PM
  3. Declaring API Functions In 64 Bit
    By marreco in forum Excel Help
    Replies: 2
    Last Post: 02-11-2013, 03:18 AM
  4. VBA to avoid - "indirect" Formula
    By leopaulc in forum Excel Help
    Replies: 2
    Last Post: 10-23-2012, 05:01 PM
  5. Avoid flickering in excel
    By Excelfun in forum Excel Help
    Replies: 3
    Last Post: 05-17-2012, 09:37 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •