(Solved) – Countif VBA – changing elements(cells) in the array without a for loop & without changing source data


I was trying to either in the =countif(A$1:A$118,B2) or in vba, do something to the range without changing the cells, column or range, before executing the formula, and I couldn’t. So I adapated a code i found to work on the array before calculating on the criteria:

 Function countifcode4(rng As Range, crit As String)
 cnt = 0

 For Each jesepeak In rng

 jesepeak2 = Chr(34) & jesepeak & Chr(34) 'so ive changed the range to values - something, in this case "values"

 s = jesepeak2 = crit
test = Evaluate(s)

If test = True Then
cnt = cnt   1
End If


countifcode4 = cnt

End Function

It does the job, but its not really an array is it? its a For loop going down a range of cells. (and it almost crashes excel if the whole range A:A is selected and I run it)

What I am looking for is a way to do this on the range in VBA at once. Work on the range without changing that appearance on the sheet & execute the countif backend with a different method ive used than a for if loop.

like here:

say B2:B11 contained fruit names,

  • is there a way of changing the values in B2:B11 without changing the cells B2:B11 and do the countif on that new modified array?

or here:

  Var = Application.WorksheetFunction.CountIf(Range("D3:" & c), "Apples-2")

could I change the elements of D:D without changing the values of the cells in the worksheet , do it in vba, & without a for loop, (in effect changing the D:D array back-end to Fruits-2 or “fruits”) and look for Apples-2 or “”Apples”” instead?

Leave a Reply

Your email address will not be published. Required fields are marked *