Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro 2Copy equations down to last row of a table

    I have a query that returns a table from a database. The table runs from column C row 3 to column J row? (depends on the number of rows returned from the query). In columns K to V, I have equations that extract from the table, concatenate values, or run Lookups (where the lookup_Value is one of the cells in the table). I need a MACRO to copy the row of equations J3:V3 down the length of the table, which I can locate using =ROW(offset($C$3,counta($D:$D)-1,1,1)
    which I would run each time I refresh the query table.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro 2Copy equations down to last row of a table

    Stephen,

    I'm not sure if you want the formulas to run from j to V or K to V, but it is not important as I think the following code might help you :

    Sub FillDown()
    Dim LastRow As Integer
    LastRow = Application.CountA(ActiveSheet.Range("C:C"))
    Range("J3:V3").Select
    Selection.AutoFill Destination:=Range("J3:V" & LastRow)
    Range("V3").Select
    End Sub

    Just replace J with K if required. Also this routine takes the last row of column C as the last row to fill. Also can be changed.

    Just in case you did not know, you may not need a macro : Select Cells J3 to V3 and just give a quick double click to the fill handle that appears at the bottom right of the selection indicator. However this requires that the cells to be filled are blank

    Andrew

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro 2Copy equations down to last row of a table

    Andrew:
    Thanks for the quick response. I inserted the macro but it balked with the line:
    Selection.AutoFill Destination:=Range("J3:V" & LastRow)
    Also, I can not use the "double click the handle" solution because I have to give the application to someone else to run and I can't expect them to do more than click some buttons.
    I need to finish this by tomorrow, so I hope you are still there.
    Stephen

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Macro 2Copy equations down to last row of a table

    Attaching a file with the macro, and it seems to work ok. What error is it giving you ?

    try the attached and let me know.
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro 2Copy equations down to last row of a table

    Operator Error! This is going to be a big help. Thank you very much.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Macro 2Copy equations down to last row of a table

    Hi,
    Depending on how much data there is in your table, you may need to change the
    Dim LastRow as Integer
    line to
    Dim LastRow as Long
    just to be on the safe side.
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro 2Copy equations down to last row of a table

    Got it.
    Thanks

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro 2Copy equations down to last row of a table

    Hi Andrew (you are out there, I hope):
    I have used this little piece of code extensively but I am now running into a problem getting it to work when I call the macro from code that is connected to a button in another WS. I am kinda stumbling along in terms of designing an efficient and effective application and have gravitated into having a main program (that I start with a click button in the first WS) which calls sub ProgA() and sub ProgB(), etc. But I am not sure when I need to insert Worksheets("whatever").Activate (before the call or as the 1st statement in the called macro) and whether or not a DIM statement should come before the Activate statement. I also suspect that I may be violating some rule by having several "Calls" in a row. Can you perhaps refer me to a good discussion on design that addresses how to tie several macros together to make an application (is it best to combine the code into one code macro and, if you have several different subs, is it best for them to be in one Module, etc.) I do have some code I could send if you think that answering a specific question would be easier. I hate to receive general questions, myself.

Posting Permissions

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