VBA: Syntax for dynamic CountIf Ranges

Question

I'll do my best to try and explain my problem, but it's still a bit fuzzy in my mind so this might not be as clear as it should be, for which I apologize in advance.

Here's the part of my code I'm having trouble with:

If Application.WorksheetFunction.countif(Range("D:D"), Cells(x, firstcolumn).Value) _
    And Application.WorksheetFunction.countif(Range("F:F"), Cells(x, firstcolumn).Value) _
    And Application.WorksheetFunction.countif(Range("H:H"), Cells(x, firstcolumn).Value) Then

The idea behind this project is to check if the values in "Cells(x, firstcolumn)" are present in columns D, F and H at the same time, and then paste the values somewhere else. However the number of columns to check for the "Cells(x, firstcolumn)" values could be changed, so values would need to be checked in any number of columns (2, 10 etc). My code works perfectly for the specified Ranges but if one is missing or more are added then it stops working.

The columns to check against are always offset by 2 from the firstcolumn and firstcolumn is always B, it will be checked against D, F, H and so on while columns C,E,G etc have other data not relevant for this part.

My best guess is to have the countif Ranges changed dynamically but I'm at a loss of when and how this should be done...

Could anyone point me towards the right direction in order to achieve this? I can post the full code if needed.

Cheers!

Just a side note, your If condition is a hack, there's no Boolean expression involved there, so VBA is coalescing the result of WorksheetFunction.CountIf into a Boolean, so your code enters the Then block only when all counts are greater than 0 - changing the code to read If Application.WorksheetFunction.CountIf(...) > 0 And ... Then would make the intent clearer. — Mathieu Guindon
– Mathieu Guindon, Commented Mar 9, 2015 at 19:20

Mathieu Guindon · Accepted Answer · 2015-03-09 19:32:22Z

2

You need to extract a function here. Something like this:

Private Function IsPresentInRange(ByVal source As Range, ByVal value As Variant) As Boolean
    IsPresentInRange = Application.WorksheetFunction.CountIf(source, value) > 0
End Function

And then you need a way to figure out what ranges you need to give it for a source parameter - that can be a function of its own, or you can hard-code them somewhere; basically you want to have a concept of a group of ranges to call that function with - this would be the simplest:

Private Function GetSourceRanges() As Collection
    Dim result As New Collection

    result.Add Range("D:D")
    result.Add Range("F:F")
    result.Add Range("H:H")
    'maintain this list here

    Set GetSourceRanges = result
End Function

Ideally you would have some logic coded there, so that you don't need to manually add ranges to that collection every time.

And then you can just iterate these ranges and determine if you get a count > 0 for all of them:

Dim sources As Collection
Set sources = GetSourceRanges

Dim result As Boolean
result = True

Dim sourceRange As Range
For Each sourceRange In sources
    result = result And IsPresentInRange(sourceRange, Cells(x, firstcolumn).Value)
Next

If result Then
    ' whatever you had in that If block
End If

answered Mar 9, 2015 at 19:32

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

vlad88667 Over a year ago

Very interesting Mat, thank you for taking time to answer my question. I'll try your idea and report back!

vlad88667 Over a year ago

Ok I tried your idea and it does work brilliantly as long as the data columns set as GetSourceRanges are populated. However if a column (for example H:H) doesn't exist the function won't return anything. I'm guessing the GetSourceRanges function needs to create the list of Ranges before the rest of the code runs based on the available data columns.

Collectives™ on Stack Overflow

VBA: Syntax for dynamic CountIf Ranges

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related