excel - 如何将某个范围的值保存到变量中

Question

我需要将某个范围内每个单元格的值保存在一个变量中，以便稍后在label.caption.

我写了下一个代码：

Dim sngNum As Single
Dim rngNum As Range

If sngEvenavg > sngOddavg Then
    For Each rngNum In Range("A9:A13")
        sngNum = rngNum
    Next rngNum
ElseIf sngEvenavg < sngOddavg Then
    For Each rngNum In Range("A47:A51")
        sngNum = rngNum
    Next rngNum
End If`

我也试过：

If sngEvenavg > sngOddavg Then
    For Each rngNum In Range("A9:A13")
        sngNum = sngNum & vbNewLine & rngNum
    Next rngNum
ElseIf sngEvenavg < sngOddavg Then
    For Each rngNum In Range("A47:A51")
        sngNum = sngNum & vbNewLine & rngNum
    Next rngNum
End If

当我sngNum在 a中显示时label.caption，它只显示范围内最后一个单元格的值，但是，我需要范围内每个单元格的所有值。

标签： excelvba

Answer 1

像这样试试

Dim vDat as variant

If sngEvenavg > sngOddavg Then
    vDat = WorksheetFunction.Transpose(Range("A9:A13").Value2)
ElseIf sngEvenavg < sngOddavg Then
    vDat = WorksheetFunction.Transpose(Range("A47:A51").Value2)
End If
' In case you have cell errors to avoid a type mismatch
Dim i As Long
For i = LBound(vDat) To UBound(vDat)
    vDat(i) = CStr(vDat(i))
Next i

vDat = Join(vDat, ";")

vDat将包含用分号分隔的范围内的所有值。