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

[Spyros13]

Because I was trying to either in the countif(A$1;A$118,B1) or in vba do something to the range WITHOUT changing the cells, column or range, before executing the formula, and I couldn't, I adapated a code i found to work on the array before calculating for tests (criteria met):

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

But its not really an array is it? its a For loop going down cells. (and it almost crashes excel if the whole range A:A is selected and I Run.

what I would really like though is a way to do this on the range in VBA as countif is usually called in VBA. i.e. work on the range from the sheet without changing that appearance on the sheet & execute the countif backend, without the for loop method I used above.

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?

or in here,

 Function MyCOUNTIF(ByVal userRange As Variant, ByVal userCriteria As Variant)
MyCOUNTIF = Application.WorksheetFunction.CountIf(userRange, userCriteria)
End Function

or in here:

Dim xRange as Range
Set xRange=Cells(Rows.Count,4).End(xlup)
Set xRange=Range(Range("d12"),xRange)
answer=Application.CountIf(xRange,"apple")

or here:

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

could I change the elements of D:D without changing the values of the cells, without loop, to look for Apples-2 or "Apples" for example?

 

[offthelip]

try this , it should be much faster because I am using a variant array instead of accessing every cell individually

Function countifcode5(rng As Range, crit As String)


cnt = 0
inarr = rng
For i = 1 To UBound(inarr, 1)
 For j = 1 To UBound(inarr, 2)
 
    If inarr(i, j) = crit Then
    cnt = cnt + 1
    End If
 Next j
Next i
    
countifcode5 = cnt


End Function

 

[Spyros13]

Weve done it !! That's wonderful. ! Thanks! Yes it works MUCH MUCH better. Wonderful code !! Thank you!

 Function countifcode55(rng As Range, crit As String)

cnt = 0
inarr = rng
For i = 1 To UBound(inarr, 1)
 For j = 1 To UBound(inarr, 2)
    inarr2 = Chr(34) & inarr(i, j) & Chr(34)
    If inarr2 = crit Then
    cnt = cnt + 1
    End If
 Next j
Next i
    
countifcode55 = cnt


End Function