0

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.

like here:

say B2:B11 contained fruit names, 

    application.WorksheetFunction.CountIf(Range(“B2:B11”,”Apple”))
  • 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?

David Wooley - AST
  • 346
  • 2
  • 4
  • 13
  • 1
    `Dim MyAr As Variant` and then `MyAr = rng.Value` This will give you the array that you need. You can access the array by looping `MyAr(i,1)` – Siddharth Rout Apr 11 '20 at 09:46
  • 1
    Also once the range is passed to the function, find the last row in that range and then construct your temp range before passing it to the array so that you do not end up passing the entire column for example `A:A`. To find last row in a range/column you may want to see [THIS](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) Alternatively find the last row and construct your range before you pass it to the relevant procedure – Siddharth Rout Apr 11 '20 at 09:49

0 Answers0