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

inside your loop, modify your line If rCell.Value = myArray Then to If rCell.Value = myArray(i) Then as you are trying to check the criteria of each cell inside the array — Shai Rado
Also, modify your Dim myArray(25) As Integer to Dim myArray(24) As Integer, otherwise the last element will be empty, and all empty cells will go inside your If criteria. — Shai Rado
@ShaiRado can you add your corrections as an answer to accept it, please! — KostasC

Shai Rado Shai Rado · Accepted Answer · 2016-10-02T12:29:16

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

Vba Excel Type mismatch

1 Answers