VBA modification instead of too many OR function, how to use array {2011, 2012, 2013}

I had this question after viewing Modification of VBA to use named range instead of offset.

I have this great piece of code from Rgonzo1971.

while the solution, i have is perfect.  for my learning purposes. i wanted to know how can i change this so that instead of many OR and AND functions, i simply use an array for example lets say if i have more years to add then i simply use the  {2011, 2012, 2013}  for year like this ShD.Cells(C.Row, intYearCol).Value = {2011, 2012, 2013}   and for months to exclude ShD.Cells(C.Row, intMonthCol).Value <> {111, 114, 115}

is this something even possible?  because i have seen it in formulas but not in VBA.

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

Open in new window

LVL 6
FloraAsked:
Who is Participating?
 
Rgonzo1971Commented:
Hi,

pls try
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 <= 2013) _
            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

Open in new window

Regards
1
 
FloraAuthor Commented:
many thanks Rgonzo1971.  this is great but what is i do not have year, instead i am checking against another string for example.

InMonthCol cannot always be lower than 111  or it can be a text.  is there a way to have something like this  include {string, string, string}  
also exclude {string, string, string}
0
 
Rgonzo1971Commented:
not possible in this way (formulas are pretty powerful tools)
1
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
FloraAuthor Commented:
ok many thanks. then it works for me.
0
 
FloraAuthor Commented:
Many thanks Rgonzo1971. much appreciated.
0
 
ProfessorJimJamCommented:
Flora,

You can use INSTR Function in VBA to include and exclude multiple strings without use of OR and AND.

like this

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 InStr(1, "^^2011^^2012^^2013^^", "^^" & ShD.Cells(c.Row, intYearCol).Value & "^^") > 0 ' change > 0  _
            And InStr(1, "^^111^^114^^115^^", "^^" & ShD.Cells(c.Row, intMonthCol).Value & "^^") = 0 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

Open in new window

1
 
FloraAuthor Commented:
Thanks Professor
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.