1
votes

So I am working on this module in VBA where I can

  1. Copy data from a worksheet range to an array.

  2. 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
6
I think it will be a variant. Also, ranges.value exists as an array, so 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 beNathan_Sav
Do you have any error values in your ranges?Rory
I dont have any error values. It's all Integers @RoryPhastOfTheFuture
@Nathan_Sav I tried to assign values by arr=range("a1:a100").value but it didn't work that way.PhastOfTheFuture
If you just need to count the occurrence of integers in a range, why don't you just use COUNTIF?. You can loop through your range and get the results that wayZac

6 Answers

1
votes

converting cell range into a 1d array may help

    Dim aaa As Variant

    aaa = Range("B2:B33")               ' 2D array (32x1)
    Debug.Print aaa(2, 1)

    aaa = Application.Transpose(aaa)    ' 1D array
    Debug.Print aaa(2)

    ' note: if you start with row data, then do a second transpose to get 1D array
0
votes

Most probably the value cannot be parsed to Integer. Just add the following to your code and see the value, for which the error is happening:

For i = 0 To 32
        Debug.Print firs.Offset(i, 0)
        Debug.Print secon.Offset(i, 0)
        Debug.Print thir.Offset(i, 0)
        first(i) = firs.Offset(i, 0)
        second(i) = secon.Offset(i, 0)
        third(i) = thir.Offset(i, 0)
Next

Possible fixes - just guessing that you can change Integer to Long or Double everywhere in your code. Or see the values in the immediate window, after you get the error.

0
votes

Some notes:

  1. Excel handles all numeric values as Double
  2. Range.Value always returns Variant/Variant() for multiple cells
  3. You can't directly cast array in VBA

Check notes on code bellow.

Option Explicit

Sub Task1()

    Dim total(32) As Integer
    'Range.Value return Variant() for multiple cells. VBA doesn't cast arrays!
    Dim first, second, third

    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")

    'Assuming you are copying data to array for performance reasons, make just a single assignement:
    first = firs.Value
    second = secon.Value
    third = thir.Value

    'Variables in VBA are always initialized, so all integers already 0%
    'For i = 0 To 32
    '    total(i) = 0
    'Next


    Call reader(total, first)

End Sub

Sub reader(total() As Integer, first)
    'This notation from VB.Net doesn't work in VBA: i and j are Variant!
    'Dim i, j, k As Integer

    'Don't undersatnd your code objective, propose new counter:
    Dim v
    For Each v In first
        'Excel only handles Double
        If VarType(v) = vbDouble Then total(v) = total(v) + 1
    Next

End Sub
0
votes

Your macro is trying to put the entire ranges (well, ranges offset by a given amount) you specify into the arrays in each operation. Since the array is not made to hold ranges, you need to change them into the values held in each cell in the range:

For i = 0 To 32
   first(i) = firs.Cells(i, 1).Value
   second(i) = secon.Cells(i, 1).Value
   third(i) = thir.Cells(i, 1).Value
Next

Or even easier:

Dim first() As Variant ' declare an unallocated array
Arr = Range("B2:B33")' Arr is now an allocated array

This page has some good info for getting started on working with arrays in VBA.

0
votes

First, your arrays are not the same size as your ranges - your arrays have 33 elements, the ranges only 32. Second, you are trying to assign multiple-cell ranges to each element of the array. Your loop should be something like this:

For i = 0 To 31
   first(i) = firs.Cells(i + 1, 1).Value
   second(i) = secon.Cells(i + 1, 1).Value
   third(i) = thir.Offset(i + 1, 1).Value
 Next

Note the use of Cells rather than Offset. (though you could Offset and Resize too)

0
votes

I had this problem when the source range was exactly one cell. I applied Chip Pearson's solution and that resolved it.

From Charles Pearson's excellent article on Arrays and Ranges:

There is a special case when the range on the worksheet is a single cell. Expanding on the code above, you should use the code below if it is possible that the range is a single cell:

Dim Arr() As Variant
Dim RangeName As String
Dim R As Long
Dim C As Long
Dim RR As Range

RangeName = "TheRange"
Set RR = Range(RangeName)
If RR.Cells.Count = 1 Then
    ReDim Arr(1 To 1, 1 To 1)
    Arr(1, 1) = RR.Value
Else
    Arr = Range(RangeName)
End If

Source: www.cpearson.com/excel/ArraysAndRanges.aspx Copyright 2018, Charles H. Pearson