#1
|
|||
|
|||
Need Help With SUMIF Formula In R1C1 Style
The formula is =SUMIF(G10:G45,,">0",F10:F45) I try to add it to a macro using the R1C1 style I get a run time error. Here is what I tried to enter.
Range ("t9") . Select ActiveCell.FormulaR1C1"=SUMIF(R[1]C[-13]:R[36]C[-13],">0",R[1]C[-14]:R[36]C[-14]) This program is about 15 years old and uses many addition and division formulas like the one above. this is the first time trying to use SUMIF in it. Any help would be great. Thanks |
#2
|
|||
|
|||
Hi,
The following works for me: Range("t9").Select ActiveCell.FormulaR1C1 = "=SUMIF(R[1]C[-13]:R[36]C[-13],"">0"",R[1]C[-14]:R[36]C[-14])" Note that the quote character ("), is used as delimiter. If you need it INSIDE your statement, you need to use double quotes (see above). In addition the "=" was missing directly after R1C1 and the quote sign at the end was also missing. I hope this solves your issue! Best regards Bjorn |
#3
|
|||
|
|||
BJORN it works for me also. Thank you Very Much for your help.
|
#4
|
|||
|
|||
Bjorn
Could you help me again? I decided I wanted to include the sum of two more colums to the formula but again what I entered did not work. Heres what I tried. Range("t9") .Select ActiveCell.FormulaR1C1 = "=SUMIF(R[1]C[-13]:R[36]C[-13],"">0"",R[1]C[-14]:R[36]C[-14])+SUM(R[1]C[-6]:R[36]C[-6])+SUM(R[1]C[-5]:R[36]C[-5])" |
#5
|
|||
|
|||
Hi Todd,
your macro works fine for me if I only remove the space after Range("t9"). Like this I mean: Range("t9").Select This gives the following formula result, when running the macro: =SUMIF(G10:G45,">0",F10:F45)+SUM(N10:N45)+SUMMA(O1 0:O45) which seems to be correct. /Bjorn |
#6
|
|||
|
|||
BJORN
In my macro I did have the space between the ) and the .Select but must have had something else out of order because I also was able to make it work after I posted. I again did not think the formula all the way thru because when I saw my result I realized I needed the two new columns to have the same SUMIF >0 critera that you help me with to be included in the result in t9. I entered the formula using the +Sumif and it only would work on the N column. I could not get it to work for the O column.Sorry for the confusion. Here the formula I tried to enter. Range("t9").Select ActiveCell.FormulaR1C1"=SUMIF(R[1]C[-13]:R[36]C[-13],"">0"",R[1]C[-14]:R[36]C[-14])+SUMIF(R[1]C[-13]:R[36]C[-13],"">0"",R[1]C[-6]:R[36]C[-6])"+SUMIF(R[1]C[-5]:R[36]C[-5])" Can you run the macro to see if you can get it to work? Thanks again Todd |
#7
|
|||
|
|||
Hi Todd,
The last SUMIF is missing a lot of info. Here is a correction that works for me: Range("t9").Select ActiveCell.FormulaR1C1 = "=SUMIF(R[1]C[-13]:R[36]C[-13],"">0"",R[1]C[-14]:R[36]C[-14])+SUMIF(R[1]C[-13]:R[36]C[-13],"">0"",R[1]C[-6]:R[36]C[-6])+SUMIF(R[1]C[-13]:R[36]C[-13],"">0"",R[1]C[-5]:R[36]C[-5])" Another (shorter) solution is: Range("t9").Select ActiveCell.FormulaR1C1 = "=SUMPRODUCT((R[1]C[-13]:R[36]C[-13]>0)*(R[1]C[-14]:R[36]C[-14]+R[1]C[-6]:R[36]C[-6]+R[1]C[-5]:R[36]C[-5]))" ... which corresponds to the more readable: =SUMPRODUCT((G10:G45>0)*(F10:F45+N10:N45+O10:O45)) Is there a special reason to why you use the R1C1-style? This code I find it easier to read: Range("t9").Select ActiveCell.Formula = "=SUMPRODUCT((G10:G45>0)*(F10:F45+N10:N45+O10:O45) )" I hope it works now for you! Kind regards Bjorn |
#8
|
|||
|
|||
Thanks again Bjorn. The reason for the R1C1 style is the macros in this work book were built using it years ago. I am only modifying it to do more than it was originally intended to do. Is their a way to convert it?
|
#9
|
|||
|
|||
Hello Todd,
I don't think there is such a possibility, and I don't think you should touch all that old code. Good luck and I hope the issue is finally closed now! Best regards Bjorn |
#10
|
|||
|
|||
Thanks again BJORN. This is something I decided to try because I had never done it before. The workbook is set up for keeping records for a year by the month. There are option for printing monthly and quarterly reports. All the formulas that you helped me with work. The next set of formulas that I have to make changes to are the quarterly reports. I will try to figure them out myself using what you have taught me so far but if I have trouble I would like to be able to contact you again for help if that's OK with you.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Character style stripped while applying paragraph style | sams_gates | Word | 0 | 08-29-2009 02:03 AM |
AutoFormat with style | zac | Word | 0 | 01-12-2009 01:12 PM |
Need to delete style and style text | mclan | Word | 0 | 08-04-2008 12:05 PM |
Style shortcut | billzant | Word | 0 | 01-06-2007 02:13 AM |
Multiple criteria in SUMIF? | pumpkin head | Excel | 1 | 02-17-2006 09:06 AM |