Assign Range Value to Array Results In Type Mismat

Question

I am looping through a row of cells and trying to assign the values in these cells to an array, but this is resulting in a Type Mismatch error. The relevant bits of my code are below:

Dim queryaddress As Range
Dim notoffsetnum As Integer
Dim anotherarrayofnumbers() As Integer
Dim c As Range
For Each queryaddress In worksheetname.Range("B2:B21")
Set queryrow = queryaddress.EntireRow
notoffsetnum = 0
For Each c In queryrow
If c.Interior.Color <> 192 And Not IsEmpty(c.Value) Then
notoffsetnum = notoffsetnum + 1
ReDim Preserve anotherarrayofnumbers(notoffsetnum)
anotherarrayofnumbers(notoffsetnum) = c.Value
'The above line errors
End If




Next c
Next queryaddress

Answer 1

A for each loop loops through a collection. You have a range called query row. You have a range called c. What you've done is loop through every RANGE in queryrow...which means c will just be query row.

You want

for each c in queryrow.cells

Also, be aware that's about as inefficient as possible since it's going to loop through all 65000 or so columns, instead of just the comparatively few that actually have data.

EDIT: I'm not sure why that's still getting you an error. You have other logical errors though. This executes for me (also, for the love of goodness, indenting!), if I throw in some data from B2:H21, for example:

Sub test()
    Dim worksheetname As Worksheet
    Set worksheetname = ActiveWorkbook.ActiveSheet

    Dim queryaddress As Range
    Dim notoffsetnum As Integer
    Dim anotherarrayofnumbers() As Integer
    Dim c As Range
    For Each queryaddress In worksheetname.Range("B2:B21")

        Dim queryrow As Range
        Set queryrow = queryaddress.EntireRow
        notoffsetnum = 0
        For Each c In queryrow.Cells
             If c.Interior.Color <> 192 And Not IsEmpty(c.Value) Then
                notoffsetnum = notoffsetnum + 1
                ReDim Preserve anotherarrayofnumbers(notoffsetnum)
                anotherarrayofnumbers(notoffsetnum - 1) = c.Value
            End If
        Next c
    Next queryaddress

    Dim i As Integer
    For i = 0 To UBound(anotherarrayofnumbers) - 1
        Debug.Print anotherarrayofnumbers(i)
    Next i
End Sub

One other problem that was easy to fix is that be default, VBA arrays are 0-based. They start at 0, and you were erroneously starting at 1. VBA won't throw an error, it'll just have element 0 be 0.

Your real problem is that after every row, you knock out the old array because notoffsetnum goes back to 0, and then you redim the array back to a size of 1. That throws away everything and at the end you've just got the last row. I ASSUME that's an error. Since this is something that comes up a lot, here's something that I think is a bit cleaner, and a little less brittle. The only assumption I make is that you start in B2, and that you have data going both down and to the right. If that's ever going to be a problem you can alter it a bit. I just think you'll find the range.end(xl...) methods a lifesaver. It takes you the cell you'd get if you pressed ctrl+arrow key, so it's a fast way to tease out the edges of ranges.

Sub BetterSolution()

    Dim ws As Worksheet
    Set ws = ActiveWorkbook.ActiveSheet

    Dim firstCell As Range
    Set firstCell = ws.Range("B2")

    Dim lastCol As Integer
    lastCol = firstCell.End(xlToRight).Column

    Dim lastRow As Integer
    lastRow = firstCell.End(xlDown).Row

    Dim lastCell As Range
    Set lastCell = ws.Cells(lastRow, lastCol)

    Dim arr() As Integer

    Dim rng As Range
    Dim index As Integer
    index = 0
    For Each rng In ws.Range(firstCell, lastCell).Cells
        index = index + 1
        ReDim Preserve arr(index + 1)
        arr(index) = rng.Value

    Next rng

End Sub

Answer 2

The problematic bit of my code was this:

Dim anotherarrayofnumbers() As Integer

This led to an error on:

anotherarrayofnumbers(notoffsetnum) = c.Value

This was because some of my c.Value values were not actually integers.

One way to solve this is changing the array to the Variant type:

Dim anotherarrayofnumbers() As Variant

But this did not work for me as I later had to perform integer operations (such as WorksheetFunction.Quartile) on the array. Instead I simply applied formatting to those c.Value values that were not integer values, so as to filter them out of my array. This resolved my issues.

So my conditional on the If block now looks like this:

If c.Interior.Color <> 192 And c.Interior.Color <> 177 And Not IsEmpty(c.Value) Then

Where the additional interior color is what I formatted the non-integer values as.