Checking 3+ ranges to qualify a value

[Masrim]

So I am not very familiar with VBA but I want to practice more with it so I created a little project to try and was wondering if anyone could give me a push in the right direction.

What I want to do: I want to check 3 or more ranges of cells ie.. b1:k1, a2:a27, b2:g9 to see if the number I am looking for is present say 1928.
If 1928 appears in any of the cells in the above ranges I want to set a different cell, say a1, as blank, if it is not then I want to set a1 as 1928.

Any suggestions?

[Simon Lloyd]

Use CountIfs, the syntax looks like this =COUNTIFS( Criteria_range-1, Criteria-1, Criteria_range-2, Criteria-2, ...) so in your case put this in A1:
=IF(COUNTIFS( b1:k1, 1928, a2:a27, 1928, b2:g9, 1928)>=1,"",1)

[Masrim]

Use CountIfs, the syntax looks like this =COUNTIFS( Criteria_range-1, Criteria-1, Criteria_range-2, Criteria-2, ...) so in your case put this in A1:
=IF(COUNTIFS( b1:k1, 1928, a2:a27, 1928, b2:g9, 1928)>=1,"",1)

I tried this out and it works great with one range. If I add a second range it just returns #value or for some odd reason in the cell I originally wanted the formula it returns the value 8 and replaces the formula. Very strange.

[Simon Lloyd]

What do you mean one range?, there's 3 ranges in that formula, can you supply a sample workbook where you have used this? Did you use my formula starting with =IF ?

[Masrim]

Hi Simon,
sorry about the long delay in responding but got a little busy at work here.

What I meant is that when I used the formula (exactly as you posted it) and with the 3 ranges included it returned #VALUE!.
With only one range used it works fine. I may just have to use 2 embedded ifs to get the result I need.

[Simon Lloyd]

If it showed #VALUE there must be something wrong with your data set or range, can you post a sample workbook with similar data in similar ranges so we can provide the answer you need?