Excel将范围加载到数组中，转换并返回到原始范围

Question

我正在将一个区域读取到一个数组中，在数组中循环以计算一些公式的值，然后将数组返回到工作表中。如果我将数组返回到与原始范围不同的范围，那么它看起来工作正常，但如果我将其返回到原始范围，我只得到第一个公式行，而不是整个范围。我错过了什么？

Sub Test2()
Dim vInArrayF As Variant
Dim vInArrayV as Variant
vInArrayF = Range("B13:M55").Formula
vInArrayV = Range("B13:M55").Value
Dim OutRange As Range
Dim R As Long
Dim C As Long
For R = 1 To UBound(vInArrayF, 1) ' First array dimension is rows.
For C = 1 To UBound(vInArrayF, 2) ' Second array dimension is columns.
If Left(vInArrayF(R, C), 3) = "=+S" Then
vInArrayF(R, C) = vInArrayV(R, C)
End If
Next C
Next R
'Set OutRange = Range("AB13:AM55")
Set OutRange = Range("B13:M55")
OutRange.ClearContents
OutRange = vInArrayF

结束子

Answer 1

使用Range.Formula2。

• Range.Formula将第一个公式应用于整列
• CCD_ 3将公式应用于阵列中的每个元素

---

Sub ReplaceSomeFormulas()
Dim vInArrayF As Variant
Dim vInArrayV As Variant
With Range("B13:M55")
vInArrayF = .Formula2
vInArrayV = .Value
Dim R As Long
Dim C As Long
For R = 1 To UBound(vInArrayF, 1)
For C = 1 To UBound(vInArrayF, 2)
If Left(vInArrayF(R, C), 3) = "=+S" Then
vInArrayF(R, C) = vInArrayV(R, C)
End If
Next C
Next R
.Formula2 = vInArrayF
End With
End Sub