我有以下代码:
Dim lRow As Long
Dim c As Variant
lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
c = Application.Transpose(ws.Range("A2:A" & lRow).Value2)
只要CCD_ 1是>2,则c
变成Variant/Variant(1 to x)
,即具有来自列a的值的Variant/String
的数组——这就是我所需要的!
然而,有时lRow
是2——这意味着c
变成了一个字符串(而不是一个有一个条目的数组(——这会使代码进一步混乱
有没有一种方法可以使用Application.Transpose(ws.Range("A2:A" & lRow).Value2)
来生成实际数组而不是Variant?或者以某种方式强制c
始终为数组?
还是我只需要对类型进行if
检查,并在整个过程中构建更多的逻辑?
我试过lRow
0,但那不是Transpose
产生的。。。
您应该先将范围读取到range变量中,然后仅当它至少有2个单元格时进行转置:
Dim lRow As Long
Dim c() As Variant
Dim rng As Range
lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Set rng = ws.Range("A2:A" & lRow)
If rng.Count > 1 Then
c = Application.Transpose(rng.Value2)
Else
ReDim c(1 To 1, 1 To 1)
c(1, 1) = rng.Value2
End If
或者,您可以使用一个单独的函数将范围中的值获取到数组中:
Private Function RangeToArray(ByVal rng As Range) As Variant()
If rng Is Nothing Then Err.Raise 91, "RangeToArray", "Range not set"
If rng.Areas.Count > 1 Then Err.Raise 5, "RangeToArray", "Multi-area range"
If rng.Count > 1 Then
RangeToArray = rng.Value2
Else
Dim arr(1 To 1, 1 To 1) As Variant
arr(1, 1) = rng.Value2
RangeToArray = arr
End If
End Function
但请注意,当将Transpose应用于1值的二维数组时,它实际上会将其转换为一维数组:
Dim lRow As Long
Dim c() As Variant
lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
c = Application.Transpose(RangeToArray(ws.Range("A2:A" & lRow))) 'c is 1-D if range has only 1 cell
所以,第一选择可能更好。
最后,你可以使用你自己的Transpose版本。查看我的阵列存储库
编辑1
如果只需要1D阵列,请使用以下方法。它适用于行和列。
Private Function OneDRangeTo1DArray(ByVal rng As Range) As Variant()
Const methodName As String = "OneDRangeTo1DArray"
If rng Is Nothing Then
Err.Raise 91, methodName, "Range not set"
ElseIf rng.Areas.Count > 1 Then
Err.Raise 5, methodName, "Multi-area range"
ElseIf rng.Rows.Count > 1 And rng.Columns.Count > 1 Then
Err.Raise 5, methodName, "Expected 1-row or 1-column range"
End If
Dim arr() As Variant
If rng.Count = 1 Then
ReDim arr(1 To 1)
arr(1) = rng.Value2
Else
Dim v As Variant
Dim i As Long
ReDim arr(1 To rng.Count)
i = 0
For Each v In rng.Value2
i = i + 1
arr(i) = v
Next v
End If
OneDRangeTo1DArray = arr
End Function
另一种可能的
c = Application.Index(ws.Range("A2:A" & lRow).Value2, Application.Evaluate("transpose(row(1:" & lRow - 1 & "))"), 1)
变体是一种类型(类似于String
、Long
、Integer
、Byte
、Double
(。但是,我猜您试图将VARIABLE强制为不同的类型(字符串?(,并作为ARRAY的一部分?
如果是这样的话,我认为这应该对你有效。它创建一个从0开始的数组,最大值为最后一行减去两个单元格。如果你想转置它,或者使它成为多维的,只需添加另一层。
lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
ReDim c(lRow - 2) As String
'if you need two dimensioanl you could experiment with this:
'ReDim c(lRow - 2, 0) As String
Dim i As Long
For i = 0 To (lRow - 2)
c(i) = ws.Range("A2").Offset(i, 0).Value2
'OR if two dimenssional
'C(i,0) =
Next i
因为我只是在寻找1D数组,所以我最终只是根据Christian Buse的答案创建了一个可重用的函数:
Public Function GetArrayFromVerticalRange(rng As Range) As Variant
If rng.Count > 1 Then
GetArrayFromVerticalRange = Application.Transpose(rng.Value2)
Else
Dim c(0) As Variant: c(0) = rng.Value2
GetArrayFromVerticalRange = c
End If
End Function
使用";垂直";术语提醒我只能在单列范围内通过!并且可以创建一个";"水平";使用transpose
的transpose
的版本