Vba Excel Type mismatch

Question

I am would like to count specific numbers from an array defined inside the function, from a range of cells. Here is my code:

Function countNumbers(cell As Range)

  Dim rCell As Range
  Dim myArray(25) As Integer
  myArray(0) = 1
  myArray(1) = 2
  myArray(2) = 3
  myArray(3) = 4
  myArray(4) = 5
  myArray(5) = 11
  myArray(6) = 12
  myArray(7) = 13
  myArray(8) = 14
  myArray(9) = 15
  myArray(10) = 21
  myArray(11) = 22
  myArray(12) = 23
  myArray(13) = 24
  myArray(14) = 25
  myArray(15) = 31
  myArray(16) = 32
  myArray(17) = 33
  myArray(18) = 34
  myArray(19) = 35
  myArray(20) = 41
  myArray(21) = 42
  myArray(22) = 43
  myArray(23) = 44
  myArray(24) = 45

  For Each rCell In cell.Cells
      For i = LBound(myArray) To UBound(myArray)
          If rCell.Value = myArray Then
              countNumbers = countNumbers + 1
          End If
      Next i
  Next rCell

End Function

The error I am getting is "Type mismatch" at the first line of the code. I am using Ms-Excel 2007. Thanks

Answer 1

Function countNumbers(cell As Range)

  Dim rCell As Range
  Dim myArray(24) As Integer

  myArray(0) = 1
  myArray(1) = 2
  myArray(2) = 3
  myArray(3) = 4
  myArray(4) = 5
  myArray(5) = 11
  myArray(6) = 12
  myArray(7) = 13
  myArray(8) = 14
  myArray(9) = 15
  myArray(10) = 21
  myArray(11) = 22
  myArray(12) = 23
  myArray(13) = 24
  myArray(14) = 25
  myArray(15) = 31
  myArray(16) = 32
  myArray(17) = 33
  myArray(18) = 34
  myArray(19) = 35
  myArray(20) = 41
  myArray(21) = 42
  myArray(22) = 43
  myArray(23) = 44
  myArray(24) = 45

  For Each rCell In cell.Cells
      For i = LBound(myArray) To UBound(myArray)
          If rCell.Value = myArray(i) Then
              countNumbers = countNumbers + 1
          End If
      Next i
  Next rCell

End Function