Hi,
I am trying to use a R1C1 Sumproduct formula with a variable sheet, but I got stuck in this problem. I try to use the string "str" as the variable, yet Excel responds with run-time error 1004.
Any hints,please?
Here the code:
Thank you very much!
For Each sh In Sheets str = sh.Name Sheets("Übersicht").Range("C" & 14 + i).Select ActiveCell.FormulaR1C1 = _ "=SUMPRODUCT((R14C='& str & '!R2C7:R71C7)*('& str & '!R1C10:R1C13=Übersicht!R[-3]C)*' & str & '!R2C10:R71C13))" Next sh
Try this...
For Each sh In Sheets Str = sh.Name Sheets("Übersicht").Range("C" & 14 + i).FormulaR1C1 = _ "=SUMPRODUCT((R14C='" & Str & "'!R2C7:R71C7)*('" & Str & "'!R1C10:R1C13=Übersicht!R[-3]C)*'" & Str & "'!R2C10:R71C13))" Next sh
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
When you refer to str you are talking to VBA so the str reference should be outside double quotes
Try
"=SUMPRODUCT((R14C='" & str & "'!R2C7:R71C7)*('" & str & "'!R1C10:R1C13=Übersicht!R[-3]C)*'" & str & "'!R2C10:R71C13))"
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Thank you for quick reply, Sixthsense and Special-K.
However, the proposed solution doesn't work. It stills reports error when it comes to the line with Sumproduct...
what is the formula suppose to look like when it is in the cell (after the macro would have entered it)
Hi meabrams, thanks for response.
420003(BayerischeMotorenwerke) is the name of a sheet and it should vary depending on the string 'str'.=SUMMENPRODUKT((C$14='420003(BayerischeMotorenwerke)'!$G$2:$G$71)*('420003(BayerischeMotorenwerke)'!$J$1:$M$1=Übersicht!$C$12)*('420003(BayerischeMotorenwerke)'!$J$2:$M$71))
Hi,
You have one too many closing brackets in your formula
Sheets("Übersicht").Range("C" & 14 + i).FormulaR1C1 = _ "=SUMPRODUCT((R14C='" & str & "'!R2C7:R71C7)*('" & str & "'!R1C10:R1C13=Übersicht!R[-3]C)*'" & str & "'!R2C10:R71C13)"
Don
Please remember to mark your thread 'Solved' when appropriate.
Hi xlnitwit,
thanks for your help, that was it!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks