Formula to include range.address only if it has been set

user_6726313 0
Viewed 46

I am using a macro to write a formula into a cell on my Excel worksheet. The formula is written into the cell using the following code, where xWF1 through wWF5 are all single-cell ranges.

xWF1.Formula = "=" & xWF2.Address(True,True) & "-" & xWF3.Address(True,True) & "-" & xWF4.Address(True,True) & "-" & xWF5.Address(True,True)

xWF2 through xWF5 are set earlier in the code, if they exist. When one of them doesn't exist, the line of code above errors.

What I would like, is for the code to write the formula to include only those ranges that exist. For example, if xWF4 doesn't exist, the formula would be xWF2-xWF3-xWF5

What would be a better way to write this code?

vba excel
2 Answers

Test Ranges For 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
user_98140690

I think if you follow @Ron_Rosenfeld's suggestion and trap for the various possibilities this one should work for you.

I don't check for 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
user_24191280
Related