Macro for column of formulas

[newshound12]

Have column of formulas from FE7 to FE26. It starts with =CD31 and
ends with =CD202. Each =CD is 9 apart from the previous cell.
Need a macro to run these formulas and convert them to values

 

[My Aswer Is This]

So your saying 9 apart:

Like CD31 then CD41


Is that correct?

 

[My Aswer Is This]

If the answer to my question is yes then try this:

Sub Cell_Values()
'Modified 11/7/2019 12:46:40 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim n As Long
n = 31
    For i = 7 To 26
        Cells(i, "FE").Value = Cells(n, "CD").Value
        n = n + 10
    Next
Application.ScreenUpdating = True
End Sub

 

[Trebor76]

Here's my attempt:

Option Explicit
Sub Macro1()

    Dim rngMyCell As Range
    Dim lngMyRow As Long
    
    Application.ScreenUpdating = False
    
    For Each rngMyCell In Range("FE7:FE26")
        If lngMyRow = 0 Then
            lngMyRow = 31
        Else
            lngMyRow = lngMyRow + 9
        End If
        rngMyCell.Formula = "=CD" & lngMyRow
    Next rngMyCell
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[newshound12]

Thanks to both of you. They both work after I made some minor changes.

 

[My Aswer Is This]

Glad we were able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Thanks to both of you. They both work after I made some minor changes.

 

[Peter_SSs]

If you are interested, here is a way that you can do them all at once.

Sub FillCells()
  With Range("FE7:FE26")
    .Formula = "=INDEX(CD$31:CD$202,ROWS(FE$7:FE7)*9-8)"
    .Value = .Value
  End With
End Sub

 

[newshound12]

Thanks Peter
It works very well.

 

[Peter_SSs]

Thanks Peter
It works very well.

You're welcome. Just another option.