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

[flora]

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.


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

[snb]

See your other thread: http://www.excelfox.com/forum/showthread.php/2198-How-can-you-convert-this-VBA-with-use-Scripting-Dictionary-so-it-becomes-fast?p=10334&viewfull=1#post10334

[flora]

thanks very much for reply and willingness to help.

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

thanks.

[Excel Fox]

You could do something like this


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

[flora]

You could do something like this


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

[flora]

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

[Excel Fox]

Instead of >0 use =0

And yes, the last pipe character | was missed at my side. Good you noticed.

[flora]

Thank you genius

[flora]

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
1935

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.
1936

[Excel Fox]

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



If WorksheetFunction.CountIf(Worksheets("Name").Range("A2:A7"), ShD.Cells(C.Row, intYearCol).Value) > 0 Then
'Your code
End If

[Excel Fox]

Also, wherever you are using code, can you please wrap it with the CODE tags. It increases readability.

[flora]

thank you very much Administrator.

this excelfox is really the best.