#1
|
|||
|
|||
Application-defined or Object-defined error
Hi:
I guess it is vary basic error but as I am new to the field, I am having hard time solving the error. Here is the what I am trying to do: I have three columns in excel sheet. One column has number and another two columns have text. I am trying to sum numbers from the number column based on matched string in text columns. I do this very often. SO created macro for this. But, it is giving me run time error:Application-defined or Object-defined error. Here is the code: Range("R8").Select ActiveSheet.Paste Range("R8").Select ActiveCell.FormulaR1C1 = "=IF(SUMIF($Q$8:$Q$469,Q8,$P$8:$P$469) = 0, """", SUMIF($Q$8:$Q$469,Q8,$P$8:$P$469) )" Range("R8").Select ActiveSheet.Copy Range("R9").Select ActiveSheet.Paste Range("R9").Select Selection.AutoFill Destination:=Range("R9:R469") Range("R9:R469").Select Greatly appreciate any help!!!! |
#2
|
|||
|
|||
look at this line:
ActiveCell.FormulaR1C1 = "=IF(SUMIF($Q$8:$Q$469,Q8,$P$8:$P$469) = 0, """", SUMIF($Q$8:$Q$469,Q8,$P$8:$P$469) )" on the left side, reference style is R1C1, on the right side is another reference style R1C1 reference style looks like this: ActiveCell.FormulaR1C1 = "=R[-3]C[-1]-R[6]C[-1]" you should use: ActiveCell.Formula ="your formula" to match the formula reference style |
#3
|
|||
|
|||
Ohh Great!!! I have wasted my 2 hours and could not figure out!! Now, It worked!!
Thanks a ton!!! |
#4
|
|||
|
|||
you're wellcome
|
#5
|
||||
|
||||
FWIW, it seems to me the code could be reduced to:
Code:
With Range("R8") .Paste .Formula = "=IF(SUMIF($Q$8:$Q$469,Q8,$P$8:$P$469)=0,"""",SUMIF($Q$8:$Q$469,Q8,$P$8:$P$469))" .AutoFill Destination:=Range("R8:R469") End With Range("R9:R469").Select
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Compile error: sub or function not defined.. | xena2305 | Excel Programming | 0 | 08-02-2011 10:17 AM |
User Defined Default Settings in Insert | J_oe | Word | 1 | 02-17-2011 10:55 AM |
Change fonts of a defined word with VBA | Michael007 | Word | 6 | 02-01-2011 04:52 PM |
TOC printing Error Bookmark not Defined | techexpressinc | Word | 0 | 12-14-2008 05:24 PM |
Defined Views in Tasks | Inga | Office | 3 | 07-20-2005 07:10 AM |