Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

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,

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

For i = 1 To numcols + 1

rng1.Offset(0, 3 * (i - 1)) = E5 * 0.01

Next

Application.ScreenUpdating

'

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,

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

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.

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

```
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
```

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.

Application.ScreenUpdating

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

'

End Sub

Any insight to what I am doing wrong is appreciated.

TosagueThird-Column.xlsx

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.

For i = 1 To numcols + 1

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

Next

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial```
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
```

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

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%"

Thank you for all of your assistance.

Tosagua

In my profile you'll find links to some articles I've written that may interest you.

Marty - MVP 2009 to 2015

Microsoft Excel

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

rng1.Offset(0, 3 * (i - 1)) = E5 * 0.01

try

rng1.Offset(0, 3 * (i - 1)).Formula = "= E5 * 0.01"