Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Run time error Macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Run time error Macro

Hi

this is the code of the macro:

Sub Macro1()
'
'
'
ActiveWindow.ScrollRow = 1
Range("D2").Select
ActiveWindow.ScrollColumn = 1
Columns("D:D").Select
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("E8").Select
ActiveWindow.ScrollRow = 477
Range("C492").Select
ActiveCell.FormulaR1C1 =
"=SUMPRODUCT(""(D2:D490)*"",(R[-490]C[7]:R[-2]C[7]))"
ActiveCell.FormulaR1C1 = _

"=SUMPRODUCT((R[-490]C[1]:R[-2]C[1]=""Blaba"")*(R[-490]C[7]:R[-2]C[7]))"
Range("C493").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=9
Range("C492").Select
ActiveCell.FormulaR1C1 = _

"=SUMPRODUCT((R[-490]C[1]:R[-2]C[1]=""thing"")*(R[-490]C[7]:R[-2]C[7]))"
Range("C493").Select
ActiveSheet.Copy
ActiveSheet.PasteSpecial
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT((R[-491]C[1]:R[-3]C[1]=""more
thing"")*(R[-491]C[7]:R[-3]C[7]))"
Range("C494").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT((R[-492]C[1]:R[-4]C[1]=""More
thing"")*(R[-492]C[7]:R[-4]C[7]))"
Range("C495").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _

"=SUMPRODUCT((R[-493]C[1]:R[-5]C[1]=""THing"")*(R[-493]C[7]:R[-5]C[7]))"
Range("C496").Select
ActiveSheet.Paste

Range("C496").Select
ActiveCell.FormulaR1C1 = _

"=SUMPRODUCT((R[-494]C[1]:R[-6]C[1]=""Thing2"")*(R[-494]C[7]:R[-6]C[7]))"
Range("C497").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _

"=SUMPRODUCT((R[-495]C[1]:R[-7]C[1]=""Thing3"")*(R[-495]C[7]:R[-7]C[7]))"
Range("C498").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT((R[-496]C[1]:R[-8]C[1]=""Thing
4"")*(R[-496]C[7]:R[-8]C[7]))"
Range("C499").Select
End Sub


is the same process Like 1000 times and the problem is a RUN TIME ERROR
1004 and I get the ActiveSheet.Paste highLight. Please any help.....
New to VB


--
John21
------------------------------------------------------------------------
John21's Profile: http://www.excelforum.com/member.php...o&userid=36983
View this thread: http://www.excelforum.com/showthread...hreadid=567040

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 380
Default Run time error Macro
The pasting seems unnecessary to me

Columns("D:D").Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("C492").FormulaR1C1 = _
"=SUMPRODUCT((R[-490]C[1]:R[-2]C[1]=""thing"")*(R[-490]C[7]:R[-2]C[7]))"
Range("C493").FormulaR1C1 = _
"=SUMPRODUCT((R[-491]C[1]:R[-3]C[1]=""more
thing"")*(R[-491]C[7]:R[-3]C[7]))"
Range("C494").FormulaR1C1 = _
"=SUMPRODUCT((R[-492]C[1]:R[-4]C[1]=""More
thing"")*(R[-492]C[7]:R[-4]C[7]))"
Range("C495").FormulaR1C1 = _
"=SUMPRODUCT((R[-493]C[1]:R[-5]C[1]=""THing"")*(R[-493]C[7]:R[-5]C[7]))"
Range("C496").FormulaR1C1 = _
"=SUMPRODUCT((R[-494]C[1]:R[-6]C[1]=""Thing2"")*(R[-494]C[7]:R[-6]C[7]))"
Range("C497").FormulaR1C1 = _
"=SUMPRODUCT((R[-495]C[1]:R[-7]C[1]=""Thing3"")*(R[-495]C[7]:R[-7]C[7]))"
Range("C498").FormulaR1C1 = _
"=SUMPRODUCT((R[-496]C[1]:R[-8]C[1]=""Thing4"")*(R[-496]C[7]:R[-8]C[7]))"
Range("C499").Select


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"John21" wrote in
message ...

Hi

this is the code of the macro:

Sub Macro1()
'
'
'
ActiveWindow.ScrollRow = 1
Range("D2").Select
ActiveWindow.ScrollColumn = 1
Columns("D:D").Select
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("E8").Select
ActiveWindow.ScrollRow = 477
Range("C492").Select
ActiveCell.FormulaR1C1 =
"=SUMPRODUCT(""(D2:D490)*"",(R[-490]C[7]:R[-2]C[7]))"
ActiveCell.FormulaR1C1 = _

"=SUMPRODUCT((R[-490]C[1]:R[-2]C[1]=""Blaba"")*(R[-490]C[7]:R[-2]C[7]))"
Range("C493").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=9
Range("C492").Select
ActiveCell.FormulaR1C1 = _

"=SUMPRODUCT((R[-490]C[1]:R[-2]C[1]=""thing"")*(R[-490]C[7]:R[-2]C[7]))"
Range("C493").Select
ActiveSheet.Copy
ActiveSheet.PasteSpecial
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT((R[-491]C[1]:R[-3]C[1]=""more
thing"")*(R[-491]C[7]:R[-3]C[7]))"
Range("C494").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT((R[-492]C[1]:R[-4]C[1]=""More
thing"")*(R[-492]C[7]:R[-4]C[7]))"
Range("C495").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _

"=SUMPRODUCT((R[-493]C[1]:R[-5]C[1]=""THing"")*(R[-493]C[7]:R[-5]C[7]))"
Range("C496").Select
ActiveSheet.Paste

Range("C496").Select
ActiveCell.FormulaR1C1 = _

"=SUMPRODUCT((R[-494]C[1]:R[-6]C[1]=""Thing2"")*(R[-494]C[7]:R[-6]C[7]))"
Range("C497").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _

"=SUMPRODUCT((R[-495]C[1]:R[-7]C[1]=""Thing3"")*(R[-495]C[7]:R[-7]C[7]))"
Range("C498").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
"=SUMPRODUCT((R[-496]C[1]:R[-8]C[1]=""Thing
4"")*(R[-496]C[7]:R[-8]C[7]))"
Range("C499").Select
End Sub


is the same process Like 1000 times and the problem is a RUN TIME ERROR
1004 and I get the ActiveSheet.Paste highLight. Please any help.....
New to VB


--
John21
------------------------------------------------------------------------
John21's Profile:
http://www.excelforum.com/member.php...o&userid=36983
View this thread: http://www.excelforum.com/showthread...hreadid=567040



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable Enda80 Excel Worksheet Functions 1 May 3rd 08 02:35 PM
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable Enda80 Excel Discussion (Misc queries) 1 May 3rd 08 10:52 AM
Macro Run-time error shirley_kee Excel Discussion (Misc queries) 1 May 3rd 06 09:50 PM
Macro time out error mike b Excel Worksheet Functions 1 December 10th 05 05:21 PM
Macro-Run-time error '6' Dave Y[_2_] Excel Programming 6 August 27th 03 02:22 AM


All times are GMT +1. The time now is 05:35 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement