Excel Paste Formula To Every Third Column

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.

rng1.Offset(0, 3 * (i - 1)).formular1c1 = "=rc[-1] * 0.01"

In my last post UserRange.Columns.Count should be UsedRange.Columns.Count.

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

The formula I gave is copiable down as well

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

Select allOpen in new window

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

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.

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

Select allOpen in new window

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

Does changing the end of formula to FormulaR1C1 = "=rc[-1]  * 1.0%" do it for you?

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

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

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