我正在使用宏將公式寫入 Excel 作業表上的單元格。使用以下代碼將公式寫入單元格，其中 xWF1 到 wWF5 都是單單元格范圍。
xWF1.Formula = "=" & xWF2.Address(True,True) & "-" & xWF3.Address(True,True) & "-" & xWF4.Address(True,True) & "-" & xWF5.Address(True,True)
xWF2 到 xWF5 在代碼的前面設定（如果存在）。當其中一個不存在時，上面的代碼行會出錯。
我想要的是撰寫公式的代碼僅包含那些存在的范圍。例如，如果 xWF4 不存在，則公式將為 xWF2-xWF3-xWF5
撰寫此代碼的更好方法是什么？
uj5u.com熱心網友回復：
測驗范圍Nothing
Option Explicit
Sub Test()
Dim xWF1 As Range, xWf2 As Range, xWf3 As Range, xWf4 As Range, xWf5 As Range
Set xWF1 = Range("A1")
'Set xWf2 = Range("A2")
'Set xWf3 = Range("A3")
'Set xWf4 = Range("A4")
'Set xWf5 = Range("A5")
' The above is irrelevant for your code.
Dim rArr As Variant: rArr = Array(xWf2, xWf3, xWf4, xWf5)
Dim dFormula As String: dFormula = "="
Dim rg As Variant
For Each rg In rArr
If Not rg Is Nothing Then dFormula = dFormula & rg.Address & "-"
Next rg
' Remove the trailing '-', or the '=' if all four ranges are 'Nothing'.
dFormula = Left(dFormula, Len(dFormula) - 1)
xWF1.Formula = dFormula
End Sub
uj5u.com熱心網友回復：
我認為，如果您遵循@Ron_Rosenfeld 的建議并捕捉各種可能性，那么這應該適合您。
我不檢查 xWF1
Public Sub TestMacro()
Dim strxWF2 As String
Dim strxWF3 As String
Dim strxWF4 As String
Dim strxWF5 As String
Dim strFormula As String
If Not (xWF2 Is Nothing) Then strxWF2 = xWF2.Address(True, True)
If Not (xWF3 Is Nothing) Then strxWF3 = xWF3.Address(True, True)
If Not (xWF4 Is Nothing) Then strxWF4 = xWF4.Address(True, True)
If Not (xWF5 Is Nothing) Then strxWF5 = xWF5.Address(True, True)
If (strxWF2 <> "") Or (strxWF3 <> "") Or (strxWF4 <> "") Or (strxWF5 <> "") Then
strFormula = "="
If (strxWF2 <> "") Then strFormula = strFormula & strxWF2
If (strxWF3 <> "") Then strFormula = strFormula & "-" & strxWF3
If (strxWF4 <> "") Then strFormula = strFormula & "-" & strxWF4
If (strxWF5 <> "") Then strFormula = strFormula & "-" & strxWF5
xWF1.Formula = strFormula
End If
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/454696.html