Solved: Insert Formula via macro

[jwise]

The size of my data (the number of columns) is variable. How would I put a formula in an additional column that added together all the previous columns?

For example, suppose my spreadsheet is 3x3 data values. I want a macro to insert "=AVG(A1:C1) " into cell D1. I can already do this if the exact range is known beforehand, but my macro is of limited use since it is tied to the exact number of columns. I want this macro to work for a variable number of columns.

I used the construct:

[vba] Range(Cells(1, 1), Cells(3, 1)) [/vba]

to copy the formula down the rows, but I don't think this will work in a formula. TIA

[Paul_Hossler]

One way ...

[VBA]
Sub InsertFormula()
Dim rData As Range
Dim sData As String

Set rData = ActiveSheet.Cells(1, 1).CurrentRegion
sData = rData.Address

rData.Cells(1, 1).End(xlToRight).Offset(0, 1).Formula = "=AVERAGE(" & sData & ")"

End Sub
[/VBA]

Paul

[jwise]

Thanks Paul. It looks like this is just what I needed.

I used Google to no avail... no doubt I used the wrong search words. This is similar to some things I saw but is different enough that I would not have figured this one out.

Thanks again.