sumif on multiple sheet via vba

[glebret]

Hi,

I have a whole bunch of sheets and they all structured the same way. The all contain: the name of my client in column a, the real amount of expenses in column B, the budget amount in column C and a line for the total. I also have a summary sheets containing the name of all my clients and I would like to add the total of each column on that sheet. I have tried to get the total for just one column but there is a problem with one line of code:

Range("A3").Offset(lSht, 1).Formula = "=(SUMIF('" & Worksheets(lSht).Name & "'!C[1], ""*Total*""," '"Worksheets(lSht).Name& "'!C[3])"

All my variables or declared and working fine since I use them elsewhere.

Can anyone help me?

Thank you

 

[pgc01]

Hi

You are using the R1C1 notation in the formula, but you are using the .Formula property of the range object, which is for the A1 notation. Try with the property .FormulaR1C1

 

[glebret]

Hi,

I've tried but it does not work.
B3 is were I want my data on my summary sheet but my problem is that all the other sheets can contain more than one "total" line (that's why I use a sumif) and they don't have the same quantity of expenses, meaning that my total line won't be the same on each sheet.

Thank you.

 

[pgc01]

Hi

I did a quick test with your formula

With a workbook with several of the default worksheets (Sheet1, Sheet2,...) and the last one named Totals,

with the worksheet Totals active,

I ran this test code and got in A4:A6:

A4: =SUMIF(Sheet1!C:C; "*Total*";Sheet1!E:E)
A5: =SUMIF(Sheet2!C:C; "*Total*";Sheet2!E:E)
A6: =SUMIF(Sheet3!C:C; "*Total*";Sheet3!E:E)

Sub Test()
Dim lSht As Long

For lSht = 1 To 3
      Range("A3").Offset(lSht, 1).FormulaR1C1 = "=SUMIF('" &  Worksheets(lSht).Name & "'!C[1], ""*Total*"",'" &  Worksheets(lSht).Name & "'!C[3])"
Next lSht
End Sub

Does this help?

 

[glebret]

Hi PGC,

Thanks, That helped a lot, I gad an extra parenthesis at the beginning of the formula, a "&" symbol and an apostrophe missing and also extra quotations marks. works great now. Thx

Best Regards

Glebret

 

[pgc01]

You're welcome. Thanks for the feedback.