Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Copy formulas to variable column lengths ?

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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 ?


  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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


  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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 ...

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Which event are you running the code in?
    Tom

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Worksheet_Calculate()

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  8. #8
    New Member
    Join Date
    Apr 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    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

  9. #9
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •