Run time error Macro

J

John21

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
 
B

Bob Phillips

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 said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Easier Way to Macro Total 4
run macro text missing 1
Using Autofilter within a macro 3
Macro to shift to next row 2
Change data with a macro 8
Looping in a range 3
Need to tailor my macro code. 9
macro to copy rows that contain a cell with a certain number 2

Top