Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-08-2011, 11:12 AM
Manit Manit is offline Application-defined or Object-defined error Windows 7 64bit Application-defined or Object-defined error Office 2007
Novice
Application-defined or Object-defined error
 
Join Date: Dec 2011
Posts: 2
Manit is on a distinguished road
Default 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!!!!
Reply With Quote
  #2  
Old 12-08-2011, 11:31 AM
Catalin.B Catalin.B is offline Application-defined or Object-defined error Windows Vista Application-defined or Object-defined error Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

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
Reply With Quote
  #3  
Old 12-08-2011, 11:42 AM
Manit Manit is offline Application-defined or Object-defined error Windows 7 64bit Application-defined or Object-defined error Office 2007
Novice
Application-defined or Object-defined error
 
Join Date: Dec 2011
Posts: 2
Manit is on a distinguished road
Default

Ohh Great!!! I have wasted my 2 hours and could not figure out!! Now, It worked!!

Thanks a ton!!!
Reply With Quote
  #4  
Old 12-08-2011, 11:49 AM
Catalin.B Catalin.B is offline Application-defined or Object-defined error Windows Vista Application-defined or Object-defined error Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

you're wellcome
Reply With Quote
  #5  
Old 12-08-2011, 07:35 PM
macropod's Avatar
macropod macropod is offline Application-defined or Object-defined error Windows 7 64bit Application-defined or Object-defined error Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

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
As you'll see, you don't need to select cells to work with them. I left the final selection in place, though, as I don't know what you want to do next.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


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
Application-defined or Object-defined error User Defined Default Settings in Insert J_oe Word 1 02-17-2011 10:55 AM
Application-defined or Object-defined error 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
Application-defined or Object-defined error Defined Views in Tasks Inga Office 3 07-20-2005 07:10 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:06 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft