CODEWITHSUNDEEP
vbaexcelexcel-formula
shkelda
shkelda

Reputation: 173

Excel macro: Modifying formula and appending segment

In an Excel worksheet, I would like to modify several formulas by deleting the last character and adding an additional criterion to it.

Basically, instead of:

=SUMIFS(A1:A10, B1:B10, "", C1:C10, "Test")

I would like to have:

=SUMIFS(A1:A10, B1:B10, "", C1:C10, "Test", C1:D10, "Test2")

Given my general lack of knowledge on VBA, I can't seem to figure it out... The following produces an error:

Sub AppendCriterion()
    Dim a As String
    a = Right(Selection.Formula, Len(Selection.Formula) - 1)
    a = Selection.Formula + ", C1:D10, "Test2")"
    Selection.Formula = a
End Sub

Upvotes: 2

Views: 191

Answers (1)

Adam Vincent
Adam Vincent

Reputation: 3821

I'll step through this for you, but in the future I recommend utilizing Debug.Print and F8 (step into) to troubleshoot your code.

Line 3:

a = Right(Selection.Formula, Len(Selection.Formula) - 1)

This is actually going to subtract 1 character from the left side.

Input: =SUMIFS(A1:A10, B1:B10, "", C1:C10, "Test")
Output: SUMIFS(A1:A10, B1:B10, "", C1:C10, "Test")

So, change Right(..) to Left(...)

Input: =SUMIFS(A1:A10, B1:B10, "", C1:C10, "Test")
Output: =SUMIFS(A1:A10, B1:B10, "", C1:C10, "Test"

Now that we have the ) removed, let's move on to Line 4 where you are appending the new criterion.

Line 4

a = Selection.Formula + ", C1:D10, "Test2")"

Some notes about this, if you want to display a quotation character in a string, you have to escape it with quotations and it gets a little confusing. Also, you're setting a = Selection.Formula + .. which is the original, and not the string you modified on line 3. Lastly, your newly added criterion is C1:D10, which will cause a calculation error, I think you want D1:D10.

a = a + ", D1:D10, ""Test2"")"

Final Answer

Sub AppendCriterion()
    Dim a As String
    a = Left(Selection.Formula, Len(Selection.Formula) - 1)
    a = a + ", D1:D10, ""Test2"")"
    Selection.Formula = a
End Sub

Upvotes: 2

Related Questions