Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Posts
    122
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Excel 2010 Maco Question

    I created a spreadsheet that I import data into so that it can be formatted to print. I need to copy several formulas into cells at the end of every row of imported data. The number of rows varies each time. How do I specify in the macro the range to copy the formulas to?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ok, let's make a couple of assumptions:
    1. Row 1 contains headers
    2. The data the formulas will use starts in Col A and ends in Col F { I'm assuming the data rows contain a fixed number of columns.}
    3. There are no blank cells in Col A.
    4. There is always at least 1 row of data.

    So you want to loop from A2 until you find a blank cell and place your formulas starting in Col G onward.

    Code:
    Sub MyFormulas()
    
         Dim lCntr    As Long
         Dim rng      As Range
    
         Set rng = Range("A2")      'Set starting point
         lCntr = 0
    
         Do
           rng.Offset(lCntr, 6).FormulaR1C1 = "=Sum(RC[-6]:RC6)"
           lCntr = lCntr + 1
         Loop Until rng.Offset(lCntr, 0).Value = ""
    
    End Sub  'MyFormulas
    FYI: Article on Row/Column Referencing.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi RG

    It can be a nightmare writing formulas in VBA code.
    What I do is have the required formulas already defined on the sheet.
    For example, we could have formulas defined in the range [G2:K2] (i.e. five formulas ).
    It is much easier to check and adjust these 'on the sheet' than make changes in VBA code.
    We could even assign a name to this 'formula range' as say, "formulas".

    The VBA code then becomes:

    sub copyFormulas()

    zLastRow = [a65536].end(xlup).row 'find last data row based on column [A] entries
    temp = "g2:g" & zLastrow 'e.g. "g2:g159"
    [formulas].copy range(temp) 'copy pre-defined formulas to range
    application.cutcopymode = false 'cancel highlighted copy-to range
    [a1].select 'put cellpointer in tidy location

    end sub

    ..just a different way

    zeddy


    Then, we work out

  4. The Following User Says Thank You to zeddy For This Useful Post:

    bvitter (2012-04-09)

  5. #4
    2 Star Lounger
    Join Date
    Apr 2001
    Posts
    122
    Thanks
    7
    Thanked 0 Times in 0 Posts
    That was exactly what I needed to get several macros to work

  6. #5
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,175
    Thanks
    47
    Thanked 982 Times in 912 Posts
    You can also set the maximum range with worksheet.usedrange.rows. This saves you looping to the bottom of the file and allows for blank cells.

    cheers, Paul

  7. #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
    FWIW, I'd use:
    Code:
    zLastRow = Cells(rows.Count, "A").end(xlup).row
    rather than hardcoding A65536 (especially in a version with over a million rows)Usedrange is unfortunately not 100% reliable in Excel - it will often include cells that are not used (but once were).
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #7
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,175
    Thanks
    47
    Thanked 982 Times in 912 Posts
    UsedRange is reset on a save in my experience.

    cheers, Paul

  9. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I have seen occasions when a workbook is saved and the UsedRange is not reset. It sometimes takes a save, quit, and restart. Though even that is not always enough. I have even deleted unused rows and unused columns, and saved and it still not reset with just a save.

    Steve

  10. #9
    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
    Agreed. 98% of the time it's fine, but it's not 100% reliable.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    If you go to the VB Immediate window and enter
    ?activesheet.usedrange.count
    ..it resets the [end][home] position even after you delete cells that were used once.
    So you don't need to Save etc.

    zeddy

  12. #11
    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
    Most of the time, yes...
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Rory

    I bow to your expertise.
    Apart from Events being turned off, I'm not sure under what conditions it doesn't work.
    Is it truly spurious, or can you give a specific example of when it doesn't.

    zeddy

  14. #13
    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
    I've never found a specific reason, if indeed it is just one cause. But when it fails, it's really hard to fix (although with the XML formats, easier).
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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