Macro to paste formula into range of cells

[japoncik]

Hi,
I need to paste this formula

=AVERAGEIFS(A1:A1000;A1:C1000;"<>0";A1:A1000;"<>A1001")

into range of cells so the changes relatively to its position e.g. in column B it will look like this

=AVERAGEIFS(B1:B1000;B1:C1000;"<>0";B1:B1000;"<>B1001")

The macro I recorded will paste formula where I need, but all references remain the same. Can anyone point out what should I do to make them change?
Cheers

 

[Jeffrey Lopez]

Welcome to the Mr. Excel Message Board!

Without more information, you have a few options...

First, where is the formula being applied? A1002? If you can provide that, there might be an easier way to create a loop to achieve this...

Now when you are working with macros, if you "recorded" it, it will deal with absolutes... so if you are pasting this formula in A1002, your formula might look something like this:

=AVERAGEIFS(R[-1001]C:R[-2]C,R[-1001]C[1]:R[-2]C[1],""<>0"",R[-1001]C:R[-2]C,""<>A1001"")

So what way that works is it is relative to where the formula is being "pasted". So in this example is Row[-1001] or Row 1, C, is the same so no OFFSET is there, to R[-2] or Row 1000...

If you want to add a Column offset, you would need to add the Offset to the "C" like you can see in the next part of the fomrula: R[-1001]C[1]:R[-2]C[1], same as before but now the Column Reference (C) has an offset of +1 or 1 over from A, is B...

So I hope that makes a little more sense... So to answer your question, it is a little more involved because you have a "text" reference in your formula ("<>A1001","<>B1001").

Dim strCol(1 To 2) As String


strCol(1) = "A"
strCol(2) = "B"


    For i = 1 To 2
    ActiveCell.FormulaR1C1 = _
    "=AVERAGEIFS(R[-1001]C:R[-2]C,R[-1001]C[1]:R[-2]C[1],""<>0"",R[-1001]C:R[-2]C,""<>" & strCol(i) & "1001"")"
        ActiveCell.Offset(0, 1).Select
    Next i


End Sub

Now this takes the text out and replaces it with an array for your two column references... The loop will go with the ActiveCell, put the formula with A where strCol(i) is, and move to the next cell over and do the same where "B" would go.

I know it might seem kind of murky, but please if you have any questions, let me know, and please back up your excel file before making any changes or running this macro.