将范围值分配给类型不匹配的数组结果 [英] Assign Range Value to Array Results In Type Mismatch

问题描述

我正在遍历一行单元格，并试图将这些单元格中的值分配给一个数组，但这会导致类型不匹配错误.我的代码的相关位如下:

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

推荐答案

每个循环的 循环遍历一个集合.您有一个称为查询行的范围.您有一个范围，称为c.您要做的是遍历queryrow中的每个RANGE ...这意味着c只会是查询行.

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.

您想要

对于queryrow.cells中的每个c

另外，请注意，这将尽可能地降低效率，因为它将遍历所有65000左右的列，而不仅仅是实际有数据的少数列.

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.

我不知道为什么那仍然让您出错.但是，您还有其他逻辑错误.如果我从B2:H21抛出一些数据，这对我来说就是执行(也是出于对善良的追求，缩进！)，例如:

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

另一个容易解决的问题是默认情况下，VBA数组基于0.它们从0开始，而您错误地从1开始.VBA不会引发错误，它只是将元素0设为0.

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.

您的真正问题是，在每一行之后，您都将原来的数组删除了，因为notoffsetnum返回0，然后将数组重新调整为1的大小.这将丢弃所有内容，最后您只是得到了最后一行.我假设这是一个错误.由于这是很多事情，因此我认为这是比较干净的东西，并且不那么脆.我做出的唯一假设是，您从B2开始，并且您的数据都向下和向右移动.如果这将成为一个问题，您可以稍作修改.我只是认为您会发现range.end(xl ...)方法可以节省生命.如果您按ctrl +箭头键，它将占用您要获得的单元格，因此这是一种找出范围边缘的快速方法.

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

这篇关于将范围值分配给类型不匹配的数组结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！