Adding two values in Excel, VBA

Question

I am new to VBA and want to add two values. The code I have is:

Cells(7 + i + 1, 6).Select

sum1 = "=SUMIFS('[" + strFil_1 + "]Tooling forecast template'!R6C20:R500C20,'[" + strFil_1 + "]Tooling forecast template'!R6C7:R500C7,""=ME1"", '[" + strFil_1 + "]Tooling forecast template'!R6C5:R500C5,""=Test Tool"", '[" + strFil_1 + "]Tooling forecast template'!R6C13:R500C13,""=" + ArrME1(i) + """, '[" + strFil_1 + "]Tooling forecast template'!R6C6:R500C6,""<>Actual tool/equipment change"", '[" + strFil_1 + "]Tooling forecast template'!R6C23:R500C23,""<>Cancelled"", '[" + strFil_1 + "]Tooling forecast template'!R6C23:R500C23,""<>Not Ordered"")"

sum2 = "=SUMIFS('[" + strFil_1 + "]Tooling forecast template'!R6C20:R500C20,'[" + strFil_1 + "]Tooling forecast template'!R6C7:R500C7,""=ME1"", '[" + strFil_1 + "]Tooling forecast template'!R6C5:R500C5,""=Hard Tool"", '[" + strFil_1 + "]Tooling forecast template'!R6C13:R500C13,""=" + ArrME1(i) + """, '[" + strFil_1 + "]Tooling forecast template'!R6C6:R500C6,""<>Actual tool/equipment change"", '[" + strFil_1 + "]Tooling forecast template'!R6C23:R500C23,""<>Cancelled"", '[" + strFil_1 + "]Tooling forecast template'!R6C23:R500C23,""<>Not Ordered"") "

ActiveCell.FormulaR1C1 = sum1 + sum2

The reason I have the sum is because if I have it as one expression then the row will be too large and VBA doesn't split it into two lines properly, if this is possible please post that as well.

The error I get with the above code is it just shows TRUE or FALSE and not the actually sum.

I would suggest not hard coding all those sheet names and values and find a more flexible solution (not sure what you are trying to do). One little change in your file will break this code. — Gaijinhunter
– Gaijinhunter, Commented Jul 27, 2011 at 7:52
I would also suggest putting a break point on that last piece of code, running your function, and seeing exactly what value sum1 and sum2 are before you add them. That can help you identify where the issue is occuring. — Gaijinhunter
– Gaijinhunter, Commented Jul 27, 2011 at 7:57

Mike Woodhouse · Accepted Answer · 2011-07-27 08:03:44Z

1

If what you want is the sum of the results of the two formulae, then I'd suggest putting the actual formulae into two actual cells and adding the results. Call one the "Hard Tool" result and the other the "Test Tool" result, say.

Alternatively, if you want to run the SUMIFS() function inside VBA then you may be able to do so with something like (for the first part, not tested)

With Workbooks(strFil_1).Worksheets("Tooling forecast template")
    sum_range = Range(.Cells(6, 20), .Cells(500, 20))
    criteria_range1 = Range(.Cells(6, 7), .Cells(500, 7))
    criteria_range2 = Range(.Cells(6, 5), .Cells(500, 5))
    criteria_range3 = Range(.Cells(6, 13), .Cells(500, 13))
    criteria_range4 = Range(.Cells(6, 6), .Cells(500, 6))
    criteria_range5 = Range(.Cells(6, 23), .Cells(500, 23))
End With

sum1 = Application.WorksheetFunction.SumIfs( _
        sum_range, _
        criteria_range1, "ME1", _
        criteria_range2, "Test Tool", _
        criteria_range3, ArrME1(i), _
        criteria_range4, """<>Actual tool/equipment change""", _
        criteria_range5, """<>Cancelled""", _
        criteria_range5, """<>Not Ordered""" _
        )

answered Jul 27, 2011 at 8:03

Mike Woodhouse

52.3k12 gold badges90 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jonas Over a year ago

Thanks, I give you the answer but is there really no easier way to just add the integer value of two strings?!

Mike Woodhouse Over a year ago

@Jonas - VBA doesn't (can't!) know that those strings are supposed to be treated as worksheet formulae. They might just as well be "Jo" and "nas". Bear in mind that VBA is a general-purpose automation tool (effectively it's just Visual Basic 6.0 in embedded form) for all Office apps - it's not an Excel-specific macro language.

Patrick Honorez · Accepted Answer · 2011-07-27 07:35:02Z

0

Don't know ehat you are trying to do, but sum1 and sum2 are obviously strings.
Therefore your last line stores in active cell a formula containing the concatenation of the 2 other formulae: "=sum1string=sum2string"

If both formulae are identical strings, the TRUE returned is indeed a correct calculation.

I suggest you take the habit of adding OPTION EXPLICIT at the top of each procedure. That will force you to DIM your variables. If you DIM sum1 as single, for instance, you will immediately catch your error.

You might get a better result with:

sum2 = "+SUMIFS(...."
ActiveCell.FormulaR1C1 = sum1 & sum2

Alternatively, you could try EVALUATE or [] on you sum1 and sum2:

ActiveCell.FormulaR1C1 = [sum1] + [sum2]

edited Jul 27, 2011 at 7:35

answered Jul 27, 2011 at 7:26

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

2 Comments

Jonas Over a year ago

Ahaa, how can I change so i get the sum of them two? If i write to cell with just one sum, example sum1, I get a number. Why dont i get a number when I do sum1+sum2?

Jonas Over a year ago

OK, let me rephrase my question because adding 2 sums just aint working. How do you write these two sums as just one sum? I will need to split the string then into two lines.

Collectives™ on Stack Overflow

Adding two values in Excel, VBA

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related