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,
TosaguaAsked:
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.

Martin LissOlder than dirtCommented:
Instead of
rng1.Offset(0, 3 * (i - 1)) = E5 * 0.01

try

rng1.Offset(0, 3 * (i - 1)).Formula = "= E5 * 0.01"
0
Martin LissOlder 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
Saqib Husain, SyedEngineerCommented:
rng1.Offset(0, 3 * (i - 1)).formular1c1 = "=rc[-1] * 0.01"
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Martin LissOlder than dirtCommented:
In my last post UserRange.Columns.Count should be UsedRange.Columns.Count.
0
TosaguaAuthor 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
Saqib Husain, SyedEngineerCommented:
The formula I gave is copiable down as well
0
Martin LissOlder 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

Open in new window

0
TosaguaAuthor 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
Saqib Husain, SyedEngineerCommented:
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
Saqib Husain, SyedEngineerCommented:
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

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
Martin LissOlder 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

Open in new window

0
TosaguaAuthor 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
Martin LissOlder than dirtCommented:
Does changing the end of formula to FormulaR1C1 = "=rc[-1]  * 1.0%" do it for you?
0
Martin LissOlder 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
TosaguaAuthor 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
Martin LissOlder 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.