PDA

View Full Version : Solved: Insert Formula via macro



jwise
01-15-2008, 06:46 PM
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:
Range(Cells(1, 1), Cells(3, 1))
to copy the formula down the rows, but I don't think this will work in a formula. TIA

Paul_Hossler
01-15-2008, 06:58 PM
One way ...


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


Paul

jwise
01-16-2008, 09:09 AM
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.