CODEWITHSUNDEEP
excelloopsvba
Rory Melough
Rory Melough

Reputation: 21

Excel macro - need to know how to loop this

I've tried to find a relevant thread for looping this macro, but am struggling to find one that's relevant. All I really want to do is enter the formula (that is already written in every cell in a column) as an array. Here's my macro for doing the first three, but I'm struggling find an easy way to loop this, say, for the first 500 rows. 

Selection.FormulaArray = _
    "=IF(ISERROR(INDEX(Sheet3!R1C1:R7000C8,SMALL(IF(Sheet3!R1C1:R495C2=R4C3,ROW(Sheet3!R1C1:R495C2)),ROW(Website!R[-9])),1)),"""",INDEX(Sheet3!R1C1:R7000C8,SMALL(IF(Sheet3!R1C1:R495C2=R4C3,ROW(Sheet3!R1C1:R495C2)),ROW(Sheet3!R[-9])),1))"
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.FormulaArray = _
    "=IF(ISERROR(INDEX(Sheet3!R1C1:R7000C8,SMALL(IF(Sheet3!R1C1:R495C2=R11C3,ROW(Sheet3!R1C1:R495C2)),ROW(Website!R[-10])),1)),"""",INDEX(Sheet3!R1C1:R7000C8,SMALL(IF(Sheet3!R1C1:R495C2=R11C3,ROW(Sheet3!R1C1:R495C2)),ROW(Sheet3!R[-10])),1))"
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.FormulaArray = _
    "=IF(ISERROR(INDEX(Sheet3!R1C1:R7000C8,SMALL(IF(Sheet3!R1C1:R495C2=R12C3,ROW(Sheet3!R1C1:R495C2)),ROW(Website!R[-11])),1)),"""",INDEX(Sheet3!R1C1:R7000C8,SMALL(IF(Sheet3!R1C1:R495C2=R12C3,ROW(Sheet3!R1C1:R495C2)),ROW(Sheet3!R[-11])),1))"
ActiveCell.Offset(1, 0).Range("A1").Select

Could anyone help me with this? I would be very grateful!

Upvotes: 0

Views: 102

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

If you already have have normal formulas in an area and you want to convert them to array formulas, select the cells and run this:

Sub MakeArray()
    Dim r As Range
    For Each r In Selection
        If r.HasFormula Then
            r.FormulaArray = r.Formula
        End If
    Next r
End Sub

Upvotes: 1

Related Questions