macro to write a formula based on changing data

Question

I need a macro that will go to sheet1 row3 find the last column of data(will always change) move back one column and write a formula for the average of that cell and 3 cells(want to be able to change macro to 4,5….. cells)  to the left and put that formula in the cell in which the macro was ran from that is another sheet(sheet2).

Example:

Sheet1 has data from A1:Z10000 (data will not have empty cells)

Desired:  a formula that will average(v3:y3)  that is in sheet2

Bill

Answer 1

Bill wrote:

Abosulute is not desired  so I used this modification  Address(False,False,,True) and miss the double comma ,, the i change it and it worked.  But for future understanding what does THAT DO?

In VBA, put the cursor on the word Address, then press F1 to look at the help page.

You can pass VBA parameters by position, like most procedural programming languages, or by name.

If you look at the help page, you will see that the 1st, 2nd and 4th arguments correspond to the parameters RowAbsolute, ColumnAbsolute and External.  These are booleans -- True or False.

By default, External is False.  Setting it to True returns the address with the file and worksheet name.  Excel ignores the file name when it is the same the cell where the formula is stored.

By default, RowAbsolute and ColumnAbsolute are True, which is why Address was returning absolute references.  Setting them both to False returns relative references.

I cannot imagine why it would matter to you, unless you intend to copy the formula down the column.  But if that's the case, you might as well allow the macro to do the work in one step.

I thought you might overlook the double comma, which takes the default for the 3rd parameter.  That is one reason why I provided the by-name alternative.  Also, some people prefer the self-documenting style.

Answer 2

Bill wrote:

I forgot this formula will be in another sheet(sheet2)

Not your fault.  You said as much initially ("put that formula in the cell in which the macro was ran from that is another sheet(sheet2)").

You also stated that you want "the average of that cell and 3 cells [...] to the left", which is demonstrated by your example ("average(v3:y3)").  I believe Jeeped's formula averages only the 3 cells to the left.

So I believe Jeeped's macro should read:

Sub mcrAvgThree()

  ActiveCell.Formula = "=average(" & Sheets("sheet1").Cells(3, Columns.Count). _

    End(xlToLeft).Offset(0, -3).Resize(1, 4).Address(external:=True) & ")"

End Sub

It might also be worth noting the assumption that there is no data in column XFD in row 3 of Sheet1.  Not likely, I'm sure.  But I think it is "good practice" to state assumptions.

[EDIT] Another assumption:  there are at least 4 columns of data, or the right-most column of data is column D or greater.  We can probably take that for granted, given your description.  But again, I like to state assumptions as a matter of "good practice".

Answer 3

I forgot this formula will be in another sheet(sheet2) ...

<*sigh*> yes, that would make a difference. This should fix the reference to the other sheet,

Sub mcrAvgThree()

    With Sheets("Sheet1")

        ActiveCell.Formula = "=average('" & .Name & "'!" & _

          .Cells(3, Columns.Count).End(xlToLeft).Offset(0, -3).Resize(1, 3).Address & ")"

    End With

End Sub

Edit the second line to properly reflect the worksheet name that you want to average. Tap Alt+Q when done to return to your worksheet.

Answer 4

I forgot this formula will be in another sheet(sheet2)

Bill

Answer 5

... Desired:  a formula that will average(v3:y3) 

You've specified a formula in the currently active cell as opposed to simply putting the average value into the cell so try this,

Sub mcrAvgThree()

  ActiveCell.Formula = "=average(" & Cells(3, Columns.Count). _

    End(xlToLeft).Offset(0, -3).Resize(1, 3).Address & ")"

End Sub