CODEWITHSUNDEEP
excelvba
Sudio
Sudio

Reputation: 155

Looping through a table and changing formula

I've got a relatively small table (2-5 rows / 7-9 columns) that I'm trying to loop through and calculate values using .formula. I'm having some issues figuring out how I refer to the absolute values of the column or row of the cell that I'm on. Here's what I've hard-coded so far:

For i = 1 To Range("GapTable").Rows.Count
    For i2 = 2 To Range("GapTable").Columns.Count
        Range("GapTable").Cells(i, i2).Formula = "=IFERROR(ROUND(AVERAGEIFS(prof_gap, series, $A21, grade, B$20),2), ""N/A"")"
    Next i2
Next i

I'm trying to figure out how to change $A21 to lock the column in as an absolute, and B$20 to lock the row in, with the formula copying to the adjacent cells correctly (the same as if you took the cursor and filled in the values). The code works now, but obviously the values are all the same. Any suggestions how I address this issue, or a better way of doing this?

Upvotes: 0

Views: 115

Answers (1)

Cyril
Cyril

Reputation: 6829

As written in my comment, you can paste over the range, where the dynamic references are to the upper left cell, e.g.,

with Range("GapTable")
    .range(.cells(1,2),.cells(.Rows.Count,.Columns.Count)).Formula = "=IFERROR(ROUND(AVERAGEIFS(prof_gap, series, $A21, grade, B$20),2), ""N/A"")"
end with

If you have to loop, you can use counters, e.g.:

dim colnum as long, rownum as long
For i = 1 To Range("GapTable").Rows.Count
    For i2 = 2 To Range("GapTable").Columns.Count
        Range("GapTable").Cells(i, i2).Formula = "=IFERROR(ROUND(AVERAGEIFS(prof_gap, series, $A" & rownum & ", grade, " & cells(20,colnum).address & "),2), ""N/A"")"
        colnum = colnum + 1
    Next i2
    rownum = rownum + 1
Next i

Note that you have to convert the column number to an address, which is why the cell() reference includes the 20.

I did not include the error handling for resetting rownum/colnum to reset back to 20/2, respectively. Just throwing out the concept if for some reason you can't just paste over the range.

Edit1: forgot the . in front of cells(1,2) in the withstatement for pasting over a range.

Upvotes: 2

Related Questions