Loop Calculate and paste results in consecutive rows

[GeeWhiz7]

Hi Folks,

I have been using a loop to calculate the results of a formula and past those results into consecutive rows in the same sheet.
Things generally work fine, but at a point something isn't quite working and I get SPILL issues and some charts lose their data as cells move...even though the range referred to is still correct.

Here is what I was using and I was hoping someone could improve on it so that it can:

• Avoid using the Selection.Insert to make a new row, but instead simply increment the paste range or cell. I tried using cells, but that seemed to break things.
• For this example, iterate through values of M in a Sub and report those values consecutively in rows below.

I've simplified a sheet that should cover what I'm trying to do below.
Idea is, in the macro (not in consecutive rows on the sheet), loop through values of m (-5 to 5 step 1).
Record each result from each loop iteration below starting in B7 (it 1), then B8 (it 2), etc.

Book1
	A	B	C	D	E
1		y=mx+b
2		y	m	x	b
3		9.2	0.1	12	8
4
5		Desired Result
6	#	y	m	x	b
7	1	-52	-5	12	8
8	2	-40	-4	12	8
9	3	-28	-3	12	8
10	4	-16	-2	12	8
11	5	-4	-1	12	8
12	6	8	0	12	8
13	7	20	1	12	8
14	8	32	2	12	8
15	9	44	3	12	8
16	10	56	4	12	8
17	11	68	5	12	8
Sheet1

Cell Formulas
Range	Formula
B3,B7:B17	B3	=(C3*D3)+E3

This is what I was doing...and it worked for a while, until it didn't...my vba skills feel like banging pots and pans at the orchestra. Thank you!

Sub Iterate()
 
    Application.ScreenUpdating = False
             
    Sheet4.Activate
    Sheet4.Range("B7:E17").ClearContents
           
    Dim m As Long
        
    For m = -5 To 5 Step 1

        Sheet1.Range("B3") = m
        
        Sheet1.Range("B7") = m

        Sheet1.Range("B3:E3").Copy
        Sheet1.Range(B7:E7).PasteSpecial Paste:=xlPasteValues, Paste:=xlPasteFormats

        Sheet1.Rows("7:7").Select
        Application.CutCopyMode = False
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        
    DoEvents
        
    Next m
    
    Sheet1.Rows(7).EntireRow.Delete
    
    Application.ScreenUpdating = True
   
End Sub

 

[bbrnx19]

Sub Iterate()
 
    Application.ScreenUpdating = False
           
    Sheet1.Activate
    Sheet1.Range("B7:E17").ClearContents
         
    Dim m As Long
    ' i is use as a counter for row in cells(row,column)
     i = 7
    For m = -5 To 5 Step 1
         ' B3 changed C3
        Sheet1.Range("c3") = m

        Sheet1.Range("B3:E3").Copy
      
        ' below use range with cells, dolumn b to e row i
        Sheet1.Range(Cells(i, 2), Cells(i, 5)).PasteSpecial Paste:=xlPasteValues, Paste:=xlPasteFormats
      
        '  or Range( "B"& i &":"&"E"& i )
        'Sheet1.Range("B" & i & ":" & "E" & i).PasteSpecial Paste:=xlPasteValues, Paste:=xlPasteFormats
        i = i + 1 ' add one
    
    DoEvents
      
    Next m
 
    Application.ScreenUpdating = True
 
End Sub

 

[GeeWhiz7]

Sub Iterate()
 
    Application.ScreenUpdating = False
          
    Sheet1.Activate
    Sheet1.Range("B7:E17").ClearContents
        
    Dim m As Long
    ' i is use as a counter for row in cells(row,column)
     i = 7
    For m = -5 To 5 Step 1
         ' B3 changed C3
        Sheet1.Range("c3") = m

        Sheet1.Range("B3:E3").Copy
     
        ' below use range with cells, dolumn b to e row i
        Sheet1.Range(Cells(i, 2), Cells(i, 5)).PasteSpecial Paste:=xlPasteValues, Paste:=xlPasteFormats
     
        '  or Range( "B"& i &":"&"E"& i )
        'Sheet1.Range("B" & i & ":" & "E" & i).PasteSpecial Paste:=xlPasteValues, Paste:=xlPasteFormats
        i = i + 1 ' add one
   
    DoEvents
     
    Next m
 
    Application.ScreenUpdating = True
 
End Sub

Thank you bbrnx19, this works nicely and shows me what I was doing wrong with Cells. I still get the #SPILL! error, so will troubleshoot that next.

 

[bbrnx19]

I still get the #SPILL! error,

try replacing

Sheet1.Range(Cells(i, 2), Cells(i, 5)).PasteSpecial Paste:=xlPasteValues, Paste:=xlPasteFormats

with

        Sheet1.Range(Cells(i, 2), Cells(i, 5)).PasteSpecial Paste:=xlPasteValues
        Sheet1.Range(Cells(i, 2), Cells(i, 5)).PasteSpecial Paste:=xlPasteFormats

 

[GeeWhiz7]

try replacing

Sheet1.Range(Cells(i, 2), Cells(i, 5)).PasteSpecial Paste:=xlPasteValues, Paste:=xlPasteFormats

with

        Sheet1.Range(Cells(i, 2), Cells(i, 5)).PasteSpecial Paste:=xlPasteValues
        Sheet1.Range(Cells(i, 2), Cells(i, 5)).PasteSpecial Paste:=xlPasteFormats

Thanks bbrnx19...I did figure out that your code when put into the bigger workbook is not the problem and works fine.
The spill error is coming from earlier in the calculations when certain input values are used so I am troubleshooting that.