0 votes
in Education by (1.7m points)
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?

JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

1 Answer

0 votes
by (1.7m points)
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

Related questions

0 votes
1 answer
0 votes
1 answer
0 votes
0 answers