3

I have a macro that adds a very long formula to one of the cells.

Is there a way to break up this formula in the VBA editor to make it easier to view and edit.

Sheet3.Select

Dim lastrow As Long

Range("D2").Formula = "=SUM(IFERROR(VLOOKUP(E2,Scores[[Values]:[Score]],2,FALSE),0)+IFERROR(VLOOKUP(H2,Scores[[Values]:[Score]],2,FALSE),0)+IFERROR(VLOOKUP(I2,Scores[[Values]:[Score]],2,FALSE),0)+IFERROR(VLOOKUP(J2,Scores[[Values]:[Score]],2,FALSE),0)+IFERROR(VLOOKUP(K2,Scores[[Values]:[Score]],2,FALSE),0)+IFERROR(VLOOKUP(L2,Scores[[Values]:[Score]],2,FALSE),0)+IFERROR(VLOOKUP(M2,Scores[[Values]:[Score]],2,FALSE),0))"

Range("D2").AutoFill Destination:=Range("D2:D" & lastrow), Type:=xlFillDefault

It looks like this:
enter image description here

I'm trying to get it to look more like this:
enter image description here

A space and underscore didn't work.

I could add a carriage return but that just adds it to the formula, I'm trying to make it easier to view inside the VBA editor.

Might some kind of CONCAT do it?

Improve this question
2
  • 1
    If you're on Office 365 then these nested IFFERROR lookups could be simplified with XLookup Commented Dec 30, 2022 at 22:21
  • 1
    Best way to break down a large formula remains to add helper columns IMO Commented Dec 30, 2022 at 22:22

2 Answers 2

Reset to default
2

The simple, direct answer is to build your formula first, by itself. Below is an artificial and contrived example but it should show the main idea.

Clearly you might better find a different way to write that formula as it seems repetitive which might mean there are ways to improve it, but I thought to start with this basic answer to your question about what your were trying to do that wasn't working.

dim myFormula as string
myFormula = "=SUM("
myFormula = myFormula & "A2"
myFormula = myformula & ",B2"
myFormula = myFormula & ",C2"
myFormula = myFormula & ")"

Range("A3").Formula = myFormula

This will also work in VBA if you prefer to use line continuations:

Dim myFormula As String
myFormula = _
    "=SUM(A2" _
    & ",B2" _
    & ",C2" _
    & ")"

Range("A3").Formula = myFormula
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

'Split' a Long Formula

Option Explicit

Sub WriteFormula()
    
    Const LastRow As Long = 20 ' just to make it compile
    
    Dim Formula As String: Formula = "" _
        & "=IFERROR(VLOOKUP(E2,Scores[[Values]:[Score]],2,FALSE),0)" & vbLf _
        & "+IFERROR(VLOOKUP(H2,Scores[[Values]:[Score]],2,FALSE),0)" & vbLf _
        & "+IFERROR(VLOOKUP(I2,Scores[[Values]:[Score]],2,FALSE),0)" & vbLf _
        & "+IFERROR(VLOOKUP(J2,Scores[[Values]:[Score]],2,FALSE),0)" & vbLf _
        & "+IFERROR(VLOOKUP(K2,Scores[[Values]:[Score]],2,FALSE),0)" & vbLf _
        & "+IFERROR(VLOOKUP(L2,Scores[[Values]:[Score]],2,FALSE),0)" & vbLf _
        & "+IFERROR(VLOOKUP(M2,Scores[[Values]:[Score]],2,FALSE),0)"
        
    'Debug.Print Formula
    
    Sheet3.Range("D2:D" & LastRow).Formula = Formula

End Sub

Result in the Formula Bar For Cell D2

=IFERROR(VLOOKUP(E2,Scores[[Values]:[Score]],2,FALSE),0)
+IFERROR(VLOOKUP(H2,Scores[[Values]:[Score]],2,FALSE),0)
+IFERROR(VLOOKUP(I2,Scores[[Values]:[Score]],2,FALSE),0)
+IFERROR(VLOOKUP(J2,Scores[[Values]:[Score]],2,FALSE),0)
+IFERROR(VLOOKUP(K2,Scores[[Values]:[Score]],2,FALSE),0)
+IFERROR(VLOOKUP(L2,Scores[[Values]:[Score]],2,FALSE),0)
+IFERROR(VLOOKUP(M2,Scores[[Values]:[Score]],2,FALSE),0)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.