# Excel Paste Formula To Every Third Column

We need our macro to paste a formula to a cell inevery third row, and then copy the formula down the columns to the end of the data.

We have tried:

Sub ThirdRow()
'
' ThirdRow Macro
'

Dim rng1 As Range
Dim numcols As Long, i As Long
Set rng1 = [F5]
numcols = Int(Columns.Count - rng1.Column) / 3
Application.ScreenUpdating = False
For i = 1 To numcols + 1
rng1.Offset(0, 3 * (i - 1)) = E5 * 0.01
Next
Application.ScreenUpdating = True

'
End Sub

But it only pastes zero as a value.
The formula in the third column, use the value in the second column to calculate the new value.

Any insight or suggesions would be appreciated.

Tosagua,
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Older than dirtCommented:
rng1.Offset(0, 3 * (i - 1)) = E5 * 0.01

try

rng1.Offset(0, 3 * (i - 1)).Formula = "= E5 * 0.01"
0
Older than dirtCommented:
My solution above will put your formula in every third column. Is that what you want? If so what do you want done after that? Also in my workbook Columns.Count is > 15,000 so numcols is set to >5000. You probably want to use UserRange.Columns.Count instead.
0
EngineerCommented:
rng1.Offset(0, 3 * (i - 1)).formular1c1 = "=rc[-1] * 0.01"
0
Older than dirtCommented:
In my last post UserRange.Columns.Count should be UsedRange.Columns.Count.
0
Author Commented:
Martin, Syed,

The formula from Sayed is easier for me to understand, and the UsedRange ishat we need, as there is another array of data following a couple of empty / separating columns.

It only pastes the formula inn the first row. However, the column formula needs to to be copied down the rows 5 through 40.

Tosagua
0
EngineerCommented:
The formula I gave is copiable down as well
0
Older than dirtCommented:
``````Sub ThirdRow()
'
' ThirdRow Macro
'

Dim rng1 As Range
Dim numcols As Long, i As Long
Dim lngRow As Long
Set rng1 = [F5]
numcols = Int(ActiveSheet.UsedRange.Columns.Count - rng1.Column) / 3
Application.ScreenUpdating = False
For i = 1 To numcols + 1
rng1.Offset(0, 3 * (i - 1)).FormulaR1C1 = "=rc[-1] * 0.01"
For lngRow = 1 To 35
rng1.Offset(lngRow, 3 * (i - 1)).FormulaR1C1 = "=rc[-1] * 0.01"
Next
Next
Application.ScreenUpdating = True

End Sub
``````
0
Author Commented:
Martin,

I changed the Row numbers to 5 to 43. Then I tried to restrict the columns range to E:GZ, which continually failed. Otherwise, it takes the forumla out to column BAB. I also tried to format the cells as a percentage (00.00%). Didn't work.
And then for some reason, the macro skips rows 6 through 9 (see attached). My current code is:

Sub ThirdColumn()
'
' ThirdRow Macro
'
Dim rng1 As Range
Dim numcols As Long, i As Long
Dim lngRow As Long
Set rng1 = [F5]
numcols = Int(ActiveSheet.UsedRange.Columns.Count - rng1.Column) / 3
Application.ScreenUpdating = False
For i = 1 To numcols + 1
rng1.Offset(0, 3 * (i - 1)).FormulaR1C1 = "=rc[-1] * 0.01%"
For lngRow = 5 To 43
rng1.Offset(lngRow, 3 * (i - 1)).FormulaR1C1 = "=rc[-1] * 0.01%"
Next
Next
Application.ScreenUpdating = True

'
End Sub

Any insight to what I am doing wrong is appreciated.

TosagueThird-Column.xlsx
0
EngineerCommented:
You have

rng1.Offset(0, 3 * (i - 1)).FormulaR1C1 = "=rc[-1] * 0.01%"
For lngRow = 5 To 43
rng1.Offset(lngRow, 3 * (i - 1)).FormulaR1C1 = "=rc[-1] * 0.01%"

first you write to rng1:offset 0 rows
then you write to rng1:offset 5 rows because lngrow=5
So the rows in between remain untouched.
0
EngineerCommented:
You actually do not have to loop from 5 to 43. You can simply assign to the entire range

For i = 1 To numcols + 1
rng1.Resize(43 - 5 + 1).Offset(0, 3 * (i - 1)).FormulaR1C1 = "=rc[-1] * 0.01%"
Next
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Older than dirtCommented:
Try this and then show me visually what you want for example cell F5 to look like.
``````Sub ThirdColumn()
'
' ThirdRow Macro
'
Dim rng1 As Range
Dim numcols As Long, i As Long
Dim lngRow As Long
Dim rngColumns As Range
Dim lngLastCol As Long

lngLastCol = Cells.Find("*", SearchOrder:=xlByColumns, LookIn:=xlValues, SearchDirection:=xlPrevious).Column
Set rng1 = [F5]

numcols = Int(lngLastCol - rng1.Column) / 3

Application.ScreenUpdating = False
For i = 1 To numcols + 1
rng1.Offset(0, 3 * (i - 1)).FormulaR1C1 = "=rc[-1] * 0.01%"
For lngRow = 1 To 38
rng1.Offset(lngRow, 3 * (i - 1)).FormulaR1C1 = "=rc[-1] * 0.01%"
Next
Next
Application.ScreenUpdating = True

'
End Sub
``````
0
Author Commented:
Martin,

Everything works as it should. But I bungled the equation, and the formatting is not correct. Prior to using the formula, I need to format the columns as a percentage to two decimals. The I need to strip off the % symbol in the formula. So that 54.3 becomes 54.30%.

The problem has become formatting every third column as 00.00%.

Tosague
0
Older than dirtCommented:
Does changing the end of formula to FormulaR1C1 = "=rc[-1]  * 1.0%" do it for you?
0
Older than dirtCommented:
Oh, I see. After the rng1.offset lines add

rng1.Offset(0, 3 * (i - 1)).NumberFormat = "0.00%"

In my code you'd also need a
rng1.Offset(lngRow, 3 * (i - 1)).NumberFormat = "0.00%"
0
Author Commented:
I am sorry about this, but I accepted your solutions and awarded the points last week. Unfortunately, I am stuck using Internet Explorer 8, which doesn’t work well on Experts Exchange, while IE 11 continually crashes. Actually IE 8 is horrible on EE. I didn't see all of the comments, until I started using someone else's computer.

Thank you for all of your assistance.

Tosagua
0
Older than dirtCommented:
You're welcome and I'm glad I was able to help. I suggest you ask a question somewhere in EE about which browsers are supported.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.