Copy formulas to variable column lengths ?

stevenmcheerful

New Member
Joined
Apr 16, 2002
Messages
20
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 ?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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
 
Upvote 0
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
 
Upvote 0
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 ...
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top