How to fill range with formula using VBA?

[deko]

I have code that creates new worksheets using automation. I need to fill a
range in each worksheet with a formula after it is created. What I
currently have looks like this:

With xlapp.Workbooks(strXlsFile).Worksheets(sn(i))
lc = .Range("A1").SpecialCells(xlCellTypeLastCell).Column
lr = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
.Cells(1).EntireRow.HorizontalAlignment = xlCenter
.Cells(1).EntireRow.Font.Bold = True
.Range("B2:B" & lr).Formula = "=AVERAGE(E2:AH2)"
End With

The problem is I don't know how many columns I'm going to have. So
"=AVERAGE(E2:AH2)" is just a guess - I could have more or less columns than
"AH". I have the value of the last column expressed as an integer in the
variable lc - but how do I use this? Is there a way to translate that value
to the corresponding letter value? The formula has to be a sting value to
fill down to the last column, correct?

This may be a simple question, but I have not worked with Excel to much...

Thanks.

 

[donna.gough]

Try something along these lines.

lcl = Left(Columns(lc).Address(0, 0), 2 + (lc < 27))
MyRange = "E2:" & lcl + "2"
Range("a7") = "=Average(" & MyRange + ")"
Donna

 

[Tom Ogilvy]

With xlapp.Workbooks(strXlsFile).Worksheets(sn(i))
lc = .Range("A1").SpecialCells(xlCellTypeLastCell).Column
lr = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
.Cells(1).EntireRow.HorizontalAlignment = xlCenter
.Cells(1).EntireRow.Font.Bold = True
.Range("B2",.Cells( lr,lc).Formula = "=AVERAGE(E2:AH2)"
End With

 

[deko]

With xlapp.Workbooks(strXlsFile).Worksheets(sn(i))

lc = .Range("A1").SpecialCells(xlCellTypeLastCell).Column
lr = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
.Cells(1).EntireRow.HorizontalAlignment = xlCenter
.Cells(1).EntireRow.Font.Bold = True
.Range("B2",.Cells( lr,lc).Formula = "=AVERAGE(E2:AH2)"
End With

Actually, the range in which the formula is inserted is okay. It's the
range specified within the formula that needs to be made dynamic.

"=AVERAGE(E2:AH2)" specifies a fixed number of columns. I need a way to
dynamically adjust up to AI or down to AG, for example. I'm wondering if I
can use a different syntax within the formula - will the R1C1 syntax work?
That way I could substitute the lc variable for AH. I suppose I could write
a function that translates the lc value into a letter value... but I doubt
that's the best soution.

 

[Tom Ogilvy]

"=AVERAGE(" & .Range(.Cells(2,5),.Cells(rw,col).Address & ")"


rw and col are integers.

 

[deko]

"=AVERAGE(" & .Range(.Cells(2,5),.Cells(rw,col).Address & ")"

This is close:

With xlapp.Workbooks(strXlsFile).Worksheets(sn(i))
.Range("B2:B" & lr).Formula = "=AVERAGE(" & xlapp.Workbooks _

(strXlsFile).Worksheets(sn(i)).Range(xlapp.Workbooks(strXlsFile).Worksheets
_
(sn(i)).Cells(2, 5),
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells _
(2, lc)).Address & ")"
End with

The problem is that the formula appearing in the worksheet looks like this:

=AVERAGE($E$2:$AG$2)

so the same formula is applied to the entire B column. The formula should
be relative to the B cell:

=AVERAGE(E2:AG2)
=AVERAGE(E3:AG3)
=AVERAGE(E4:AG4)

and so on.

Is there a way to make the range relative?

 

[Tom Ogilvy]

"=AVERAGE(" & .Range(.Cells(2,5),.Cells(rw,col).Address(0,0) & ")"

 

[deko]

"=AVERAGE(" & .Range(.Cells(2,5),.Cells(rw,col).Address(0,0) & ")"

That's the ticket. 280 characters, but it works.

xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range("C2:C" & lr).Formula =
"=STDEV(E2:" &
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Range(xlapp.Workbooks(strXlsFi
le).Worksheets(sn(i)).Cells(2, 5),
xlapp.Workbooks(strXlsFile).Worksheets(sn(i)).Cells(2, lc)).Address(0, 0) &
")"

Thanks for the help!