VBA -- How to loop over columns and insert array formulas

Question

I have the following redundant code:

Sheets("Data").Range("D8").FormulaArray = "=IFERROR(INDEX(data_range, MATCH(D3&$C8, client_range & date_range, 0),MATCH(D2, name_range, 0)), ""Error"")"

Sheets("Data").Range("E8").FormulaArray = "=IFERROR(INDEX(data_range, MATCH(E3&$C8, client_range & date_range, 0),MATCH(E2, name_range, 0)), ""Error"")"

Sheets("Data").Range("F8").FormulaArray = "=IFERROR(INDEX(data_range, MATCH(F3&$C8, client_range & date_range, 0),MATCH(F2, name_range, 0)), ""Error"")"

Sheets("Data").Range("G8").FormulaArray = "=IFERROR(INDEX(data_range, MATCH(G3&$C8, client_range & date_range, 0),MATCH(G2, name_range, 0)), ""Error"")"

Sheets("Data").Range("H8").FormulaArray = "=IFERROR(INDEX(data_range, MATCH(H3&$C8, client_range & date_range, 0),MATCH(H2, name_range, 0)), ""Error"")"

Sheets("Data").Range("I8").FormulaArray = "=IFERROR(INDEX(data_range, MATCH(I3&$C8, client_range & date_range, 0),MATCH(I2, name_range, 0)), ""Error"")"

Sheets("Data").Range("J8").FormulaArray = "=IFERROR(INDEX(data_range, MATCH(J3&$C8, client_range & date_range, 0),MATCH(J2, name_range, 0)), ""Error"")"

Sheets("Data").Range("K8").FormulaArray = "=IFERROR(INDEX(data_range, MATCH(K3&$C8, client_range & date_range, 0),MATCH(K2, name_range, 0)), ""Error"")"

Sheets("Data").Range("L8").FormulaArray = "=IFERROR(INDEX(data_range, MATCH(L3&$C8, client_range & date_range, 0),MATCH(L2, name_range, 0)), ""Error"")"

Sheets("Data").Range("M8").FormulaArray = "=IFERROR(INDEX(data_range, MATCH(M3&$C8, client_range & date_range, 0),MATCH(M2, name_range, 0)), ""Error"")"

Is there a way that I can make this code more compact and maintainable by looping over the columns?

Thanks!

If you have working code which merely needs improvements then you're probably in the wrong place with this post. Code Review is where they handle existing / working code and do their upmost to improve it in terms of speed, security, sustainability, and longevity including best-practices. Give it a try. They're good! — Ralph
– Ralph, Commented Aug 23, 2016 at 13:59

z32a7ul · Accepted Answer · 2016-08-23 21:29:45Z

1

You need to use Cells instead of Range as parent of FormulaArray and Address to calculate the formula dynamically:

Dim C As Long: For C = 4 To 13 ' Column 'D' = Column 4
    Sheets("Data").Cells(8,C).FormulaArray = "=IFERROR(INDEX(data_range, MATCH(" & Sheets("Data").Columns(3,C).Address(False, False) & "&$C8, client_range & date_range, 0),MATCH(" & Sheets("Data").Columns(2,C).Address(False, False) & ", name_range, 0)), ""Error"")"
Next C

Revised Code:

Dim C As Long: For C = 4 To 13 ' Column 'D' = Column 4
    ActiveSheet.Cells(C, 8).FormulaArray = "=IFERROR(INDEX(data_range, MATCH(" & ActiveSheet.Cells(C, 3).Address(False, False) & "&$C8, client_range & date_range, 0),MATCH(" & ActiveSheet.Cells(C, 2).Address(False, False) & ", name_range, 0)), ""Error"")"
Next C

Of course, you may want to use stay with Sheets("Data") instead of ActiveSheet depending on your working environment.

edited Aug 23, 2016 at 21:29

answered Aug 23, 2016 at 14:18

z32a7ul

3,8474 gold badges24 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

z32a7ul Over a year ago

Sorry, there are two errors in the code: (1) Subscript out of range - this is because Sheets("Data") is not present in a new workbook, (2) once I replaced it with ActiveSheet, I got 'Application defined or object defined error' - and that's because I should have written Cells instead of Columns. So, I updated my reply with revised code.

z32a7ul Over a year ago

And (3) I swapped rows and columns.

equanimity Over a year ago

The revised code works very well. Note that I replaced Cells(8,C) with Cells(C,8), otherwise the iteration is row-wise rather than column-wise. Thanks again for your help.

CallumDA · Accepted Answer · 2016-08-23 14:18:04Z

1

Here is one way to go about it. Any questions just ask:

Sub DoSomething()
    Dim sRange1 As String, sRange2 As String, sRange3 As String
    Dim i As Integer

    For i = 4 To 13
        sRange1 = Cells(8, i).Address
        sRange2 = Cells(3, i).Address
        sRange3 = Cells(2, i).Address
        Sheets("Data").Range(sRange1).FormulaArray = "=IFERROR(INDEX(data_range, MATCH(" & sRange2 & "&$C8, client_range & date_range, 0),MATCH(" & sRange3 & ", name_range, 0)), ""Error"")"
    Next i
End Sub

answered Aug 23, 2016 at 14:18

CallumDA

12.1k6 gold badges33 silver badges52 bronze badges

Comments

bdpolinsky · Accepted Answer · 2016-08-23 14:22:13Z

I'd use the range property offset as seen here https://msdn.microsoft.com/en-us/library/office/ff840060.aspx . Offset it based on the increment of the loop edit:

 for i = 0 to range.("d8").end(xlRight)
 Sheets("Data").range("d8").offset(0, i).FormulaArray = "=IFERROR(INDEX(data_range, match(Sheets("Data").range("d8").offset(-5,i) & Sheets("Data").range("c8"), client_range & date_range, 0), Match(Sheets("Data").range("d8").offset(-6,i), name_range, 0)), ""Error"")"    next i

The function starts at cell D8, and continually offsets it by 1 in the column; so it puts the formula into d8 on the first iteration, e8 on the second, f8 the third, and so on.

It seems that on each of these iterations you're looking for data which is in the third row of that column (IE D3 on the first iteration) and the second row (IE D2 on the second iteration). Basically my proposed solution replaces each cell reference with

Sheets("Data").range("d8").offset(X,i)

where X changes based on which row you are seeking to retrieve information from; in the case of what you've written either row 8 (X = 0), row 3 (x = -5), or row 2 (x = -6)

Slai · Accepted Answer · 2016-08-24 03:35:09Z

-1

I don't think you need loop for that. .Formula adjusts the relative rows and columns that don't have $

Sheets("Data").Range("D8:M8").FormulaArray = "=IFERROR(INDEX(data_range, MATCH(D3&$C8, client_range & date_range, 0),MATCH(D2, name_range, 0)), ""Error"")"

Update

For Each c in Split("D E F G H I J K L M")
    Sheets("Data").Range(c & "8").FormulaArray = "=IFERROR(INDEX(data_range, MATCH(" & c & _
        "3&$C8, client_range & date_range, 0),MATCH(" & c & "2, name_range, 0)), ""Error"")"
Next

or

For Each cell in Sheets("Data").Range("D8:M8")
    c = Chr(64 + cell.column)  ' Asc("A") is 65  ' or c = Left(cell.Address(0,0))
    cell.FormulaArray = "=IFERROR(INDEX(data_range, MATCH(" & c & _
        "3&$C8, client_range & date_range, 0),MATCH(" & c & "2, name_range, 0)), ""Error"")"
Next

edited Aug 24, 2016 at 3:35

answered Aug 23, 2016 at 21:04

Slai

23k5 gold badges49 silver badges55 bronze badges

1 Comment

z32a7ul Over a year ago

In this case the whole D8:M8 range would be considered to contain one formula, which is calculated at once and can show a result array of 10×1 values. You cannot delete the formula from individual cells. The code in the question creates 10 different formulas next to each other, which are calculated separately and each of which returns an array of 1×1 values. You can delete these cells one by one. However, it is still not the same as changing the Formula property because some worksheet functions are calculated in a different way (e.g. =SUM(ROW(1:2)) returns 1 as Formula and 3 as FormulaArray).

Collectives™ on Stack Overflow

VBA -- How to loop over columns and insert array formulas

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related