in Education by
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
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
    Below, I have code that sends a personalized SMS message and includes the name. I got that part to ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked May 17, 2022 in Education by JackTerrance
0 votes
    Below, I have code that sends a personalized SMS message and includes the name. I got that part to ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked May 13, 2022 in Education by JackTerrance
0 votes
    We have over 10 computers on our network and everyone's computer opens up the file with no issue. ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked May 13, 2022 in Education by JackTerrance
0 votes
    I have a column array with the following values in my sheet: 11, 15, 5, 7, 2. I want to ... , JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Mar 11, 2022 in Education by JackTerrance
0 votes
    I have a column array with the following values in my sheet: 11, 15, 5, 7, 2. I want to ... , JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Mar 11, 2022 in Education by JackTerrance
0 votes
    I would like to move an entire row to the bottom of a spread sheet if the column k contains a 0 ... , JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 21, 2022 in Education by JackTerrance
0 votes
    I have set up a new, empty, modeless userform, to fix my problem with the least amount of code ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked May 1, 2022 in Education by JackTerrance
0 votes
    9. Cancel button (x on formula bar) can be used to undo a cell entry after it has been completed (a) True (b) ... ) Alt+P (d) Ctrl+P Select the correct answer from above options...
asked Dec 13, 2021 in Education by JackTerrance
0 votes
    I have a table: Using vba I need to add a column after UsedRange, add a header and fill cells ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 26, 2022 in Education by JackTerrance
0 votes
    I have a table: Using vba I need to add a column after UsedRange, add a header and fill cells ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Apr 26, 2022 in Education by JackTerrance
0 votes
    I have 10 worksheets. I want to create a table for each. every table has a different amount of ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Jun 2, 2022 in Education by JackTerrance
0 votes
    How do I transfer data from a worksheet to another based on a specific cell value? I tried several ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked May 22, 2022 in Education by JackTerrance
0 votes
    I am trying to do some validation on the file name before it's saved. Here are my three file- ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked May 14, 2022 in Education by JackTerrance
0 votes
    How do I transfer data from a worksheet to another based on a specific cell value? I tried several ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked May 7, 2022 in Education by JackTerrance
0 votes
    I have encountered a problem during my work. There are over one hundred worksheets in my excel, and I ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked May 7, 2022 in Education by JackTerrance
...