So I am working on this module in VBA where I can
Copy data from a worksheet range to an array.
count the number of occurances of every integer in that array.
I tried to compare Integer Array elements with integer type but it gave a type mismatch error so i tried to find out the datatype for range.offset.value but it always gave type mismatch.
Here is my code. Please help!!!
Edit:
I tried to convert all the arrays to Variant but now the If statment gives me a type mismatch error. If k = first(j) Then total(k) = total(k) + 3
'-------------------------
Option Explicit
Sub Task1()
Dim total(32) As Integer
Dim first(32) As Integer
Dim second(32) As Integer
Dim third(32) As Integer
Dim firs As Range
Dim secon As Range
Dim thir As Range
Set firs = Range("B2:B33")
Set secon = Range("C2:C33")
Set thir = Range("D2:D33")
Dim i As Integer
'copying data from first , second and third range to specific arrays
'gives type mismatchy error here
For i = 0 To 32
first(i) = firs.Offset(i, 0).Value
second(i) = secon.Offset(i, 0).Value
third(i) = thir.Offset(i, 0).Value
Next
'initialize total array with 0
For i = 0 To 32
total(i) = 0
Next
Call reader(total, first)
End Sub
'---------------------------------------------------
Sub reader(total() As Integer, first() As Integer)
Dim i, j, k As Integer
'Checks the occurance of every array element
For i = 0 To 32
'gives type mismatch error here
k = first(i)
j = i + 1
For j = i To 32
If k = first(j) Then total(k) = total(k) + 3
Next
Next
End Sub
arr=range("a1:a100").value
would save you a loop also. In the immediate window, type? typename(first(I))
also only k is defined as an integer, I and j wont be – Nathan_SavCOUNTIF
?. You can loop through your range and get the results that way – Zac