arrays - 数组中的类型不匹配

Question

我有这段代码将所选范围的值存储在数组中。

dim lRow, i as Integer
dim rngValues() as Variant

rngValues = thisworkbook.Sheets(2).Range("C2:C" & lRow)


For i = LBound(rngValues) To UBound(rngValues)
    Debug.Print rngValues(i) ' this lines returns Type Mismatch error.
Next i

对列进行一些修改后，我必须将其粘贴回列。请您帮我解决这个问题吗？

Answer 1

编辑以返回特定格式的日期值

正如 Tim Williams 已经说过的，您的 rngValues 数组是二维数组

要将其作为一维数组，您可以编写如下代码:

dim lRow A Long, i as Long '<--| expliciltly declare each variable and use Long for row index ones since they can exceed Integer capacity
dim rngValues as Variant '<--| declare rngValues as a Variant instead as an array of Variant

rngValues = Application.Transpose(ThisWorkbook.Sheets(2).Range("C2:C" & lRow).Value) '<--| transpose the "columned" values to "rowed" ones suitable for a 1D array

For i = LBound(rngValues) To UBound(rngValues)
    Debug.Print Format(rngValues(i),"yyyy/mm/dd")
Next