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 Next 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.
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?
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?