Copy formulas to variable column lengths ?

[stevenmcheerful]

say I have columns of data that look something like this:

Date num cum avg weighted avg
1/18/02 3 3.732051 4.383049
1/19/02 1 2.414214 4.383049
1/20/02 2 3.146264 4.383049
1/21/02 1 2.414214 4.383049
1/22/02 3 3.732051 4.383049
1/23/02 3 3.732051 4.09845
1/24/02 2 3.146264 4.157029
1/25/02 4 4.236068 4.230234


and I have data from column A through col. M
and formulas in cols. C2,D2,E2 through M2.

The data will come from an ASP application, and the
LENGTH of the columns is variable. How would I
determine the number of rows in Column A (ex. 72) and then
copy the formulas in C2:M2 only down 72 times ?

How would I loop across the columns of
my worksheet to copy formulas down to
as far as my data will go ?

What event do I want to run my VBA macro ?
Would it be the Calculate event of the Worksheet ?

[Tom Schreiner]

Hi
The average function ignores blanks.
It should not matter how many items are in a row for this particular function...
If you are concerned about displaying function returns in cell c78 when there is no data in a78, you could place your formula in an "if" statement:

=If(A78 <> "",Sum(A68:A78),"")

If A78 does not equal blank then go function, else blank...

If you have other reasons for filling your columns with formulas conditionally, you can use the FillDown method via VBA:

Private Sub Worksheet_Calculate()
Dim NumItemsRowA As Long
Application.EnableEvents = False
Application.ScreenUpdating = False

'will determine last row of data in column A
NumItemsRowA = Range("a1:a" & Range("A65536").End(xlUp).Row).Rows.Count
'fills down C2:M? where ? = Number of rows filled with data in Column A
Range("C2:M" & NumItemsRowA).FillDown

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

I did not test this with an external update.
You may need to run from a command button?

Tom

[stevenmcheerful]

Thanks - it is not a lot of code and works great.

My reasons for filling down only so far is
that the file size becomes way too big if one is entering formulas and (potentially) doing calculations on cells that will never hold data.

The number of days is the driver of how long the columns will be, and the number of days will vary considerably.

Some worksheets will differ from others (i.e. have different calc formulas. Is it possible to load just a worksheet from a template ?

I know you can load a workbook via template, but what if I want to load separate worksheets via different .XLT files - is that possible ?

thanks so much

[stevenmcheerful]

this looks good - but it only seems
to work the first time data is entered
into the worksheet ... why would that be ?

if I delete the range A2:B72 i.e. I remove the date and accompanying number on that date, and then paste a new range of dates and numbers, it does not fill down the columns like it did when I first opened the workbook

... strange that ...

[Tom Schreiner]

Which event are you running the code in?
Tom

[stevenmcheerful]

Worksheet_Calculate()

[Tom Schreiner]

Try this Steve:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NumItemsRowA As Long
If Target.Column < 3 Then
Application.EnableEvents = False
Application.ScreenUpdating = False
NumItemsRowA = Range("a1:a" & Range("A65536").End(xlUp).Row).Rows.Count
If NumItemsRowA < 2 Then
Application.EnableEvents = True
Application.ScreenUpdating = True
Exit Sub
End If
Range("C2:M" & NumItemsRowA).FillDown
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

Tom

[ This Message was edited by: TsTom on 2002-04-22 11:42 ]

[stevenmcheerful]

wow - that is very good Tom

I have 2 questions, and then I will let you go altogether on this:

what are you doing in the first part of the
Sub - where you check the column number ?

And also, before I tried your worksheet Change() Sub, if I deleted my dates and associated data counts, I would lose my formulas in Row 2 - they would be overwritten by the Column Heading text in Row 1 - sound familiar ?

Thanks for all of your help - I now have a viable solution.

Thanks again Tom

- steve

[Tom Schreiner]

Hi Steve

If Target.Column < 3 Then

Should probably be:

If Target.Column = 1 Then

...where Target = the changed range and Target.Column = the column we are going to be looking for a change as far as this code is concerned...

In essence, if the change occurs in column one or A then run the code...

Yes I chuckled a bit when I noticed what my original non solution accomplished.

I'm learning...
Have a Nice Day,
Tom