Loop Calculate and paste results in consecutive rows

GeeWhiz7

Board Regular
Joined
Nov 22, 2021
Messages
214
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
ABCDE
1y=mx+b
2ymxb
39.20.1128
4
5Desired Result
6#ymxb
71-52-5128
82-40-4128
93-28-3128
104-16-2128
115-4-1128
12680128
137201128
148322128
159443128
1610564128
1711685128
Sheet1
Cell Formulas
RangeFormula
B3,B7:B17B3=(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!

VBA Code:
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
VBA Code:
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
 
Last edited:
Upvote 0
Solution
VBA Code:
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.
 
Upvote 0
I still get the #SPILL! error,

try replacing
VBA Code:
Sheet1.Range(Cells(i, 2), Cells(i, 5)).PasteSpecial Paste:=xlPasteValues, Paste:=xlPasteFormats
with
VBA Code:
        Sheet1.Range(Cells(i, 2), Cells(i, 5)).PasteSpecial Paste:=xlPasteValues
        Sheet1.Range(Cells(i, 2), Cells(i, 5)).PasteSpecial Paste:=xlPasteFormats
 
Upvote 0
try replacing
VBA Code:
Sheet1.Range(Cells(i, 2), Cells(i, 5)).PasteSpecial Paste:=xlPasteValues, Paste:=xlPasteFormats
with
VBA Code:
        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.
 
Upvote 0

Forum statistics

Threads
1,223,947
Messages
6,175,559
Members
452,652
Latest member
eduedu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top