Jump X amount of Columns and paste formula within a for loop

drop05

Active Member
Joined
Mar 23, 2021
Messages
285
Office Version
  1. 365
Platform
  1. Windows
Hello, I wanted to see that within a for loop in VBA, is there a way to start at a cell then every 4 columns over until the last column in use

So starting at F44 it copies that formula there, pastes it every 4 columns now, so J44, N44, R44, and so on

thank you in advance!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Which row are you using to detemine the last used column?
 
Upvote 0
Which row are you using to detemine the last used column?
its would only be happening on row 44 starting from F44 where would copy the formula from then 4 columns over start pasting so j44, N44
would think that using in the logic would help determine the last column

Cells(1, Columns.Count).End(xlToLeft).Column
 
Upvote 0
In that code you are using row 1 to determine the last used column, so based on that and starting in column F you could use this.
VBA Code:
Dim col As Long

    For col = 6 To Cells(1, Columns.Count).End(xlToLeft).Column Step 4
        Cells(44, col).FormulaR1C1 = "=R1C"
    Next col
 
Upvote 0
In that code you are using row 1 to determine the last used column, so based on that and starting in column F you could use this.
VBA Code:
Dim col As Long

    For col = 6 To Cells(1, Columns.Count).End(xlToLeft).Column Step 4
        Cells(44, col).FormulaR1C1 = "=R1C"
    Next col
Yes sorry, i posted that as an example of what i thought could be used, but yes would like to start from column F
what does R1C1 do?

this is the logic i added it to

VBA Code:
Sub Formula_change()

Dim col As Long

Dim sch As Workbook
Set sch = Application.ThisWorkbook
Dim sch_ws As Worksheet

Dim sch1 As Worksheet
Set sch1 = sch.Worksheets("Page 1")

    For col = 6 To Cells(1, Columns.count).End(xlToLeft).Column Step 4
        Cells(44, col).FormulaR1C1 = "=R1C"        
    Next col

End Sub

I have the formula i want in cell F44 and it is not pasting in the cells after, j44, n44 etc
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

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