EXCEL VBA formula error with sheet reference

Question

Could someone please identify where the error is?

Sub calc_external_sales()
    Sheets("Monetary All").[C5].Formula = "=SUMMEWENNS(Rawdata!K2:K3446;Rawdata!I2:I3446;""bezahlt"")"
End Sub

I guess it has to do with the sheets and the range.

JosieP · Accepted Answer · 2013-06-06 09:15:13Z

1

perhaps

Sub calc_external_sales()
    Sheets("Monetary All").[C5].Formula = "=SUMIF(Rawdata!K2:K3446,Rawdata!I2:I3446,""bezahlt"")"
End Sub

or

Sub calc_external_sales()
    Sheets("Monetary All").[C5].FormulaLocal = "=SUMMEWENNS(Rawdata!K2:K3446;Rawdata!I2:I3446;""bezahlt"")"
End Sub

answered Jun 6, 2013 at 9:15

JosieP

3,4101 gold badge15 silver badges16 bronze badges

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

Comments

rwisch45 · Accepted Answer · 2013-06-06 13:07:28Z

0

The problem is that when you are using the quotes around your search criteria ("bezahlt"), it is effectively treating it as two strings that are adjacent to each other with no concatenation. Use this instead:

Sub calc_external_sales()
    Sheets("Monetary All").[C5].Formula = "=SUMMEWENNS(Rawdata!K2:K3446,Rawdata!I2:I3446," & Chr(34) & "bezahlt" & Chr(34) & ")"
End Sub

The chr(34) is the symbol for quotation marks.

answered Jun 6, 2013 at 13:07

rwisch45

3,7022 gold badges27 silver badges36 bronze badges

Collectives™ on Stack Overflow

EXCEL VBA formula error with sheet reference

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related