+ Reply to Thread
Results 1 to 9 of 9

VBA to fill formula down till last row in column

  1. #1
    Registered User
    Join Date
    05-16-2016
    Location
    bangalore,India
    MS-Off Ver
    2013
    Posts
    32

    VBA to fill formula down till last row in column

    i want to apply the formula down till the last used rows of column H.

    tat.PNG

    The formula is F3 - E3 and this formula is to be applied to the column H. Also how do i round down the number in column H.

    Below is the code:

    ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-3]"
    Range("H3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    ActiveCell.Activate
    ActiveSheet.Paste

    Its applying the formula only to H3 not to the below rows, as u can see in the above pic.
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: VBA to fill formula down till last row in column

    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    05-16-2016
    Location
    bangalore,India
    MS-Off Ver
    2013
    Posts
    32

    Re: VBA to fill formula down till last row in column

    Hi TMS,

    The code is working but its not round downing the numbers .

    ex: if the number is 9.73 its should round off to 10.

    below is the pic :

    roundoff.PNG

    Thanks !

  4. #4
    Forum Contributor IonutC's Avatar
    Join Date
    01-28-2016
    Location
    Bucharest, Romania
    MS-Off Ver
    Office 2019
    Posts
    486

    Re: VBA to fill formula down till last row in column

    can u try this? let's see if is working

    Please Login or Register  to view this content.
    Please consider:
    Be polite. Thank those who have helped you.
    Click the star icon in the lower left part of the contributor's post and add Reputation. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    Posting code between tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: VBA to fill formula down till last row in column

    Please Login or Register  to view this content.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: VBA to fill formula down till last row in column

    dunno if it will work
    No, it won't.

  7. #7
    Registered User
    Join Date
    05-16-2016
    Location
    bangalore,India
    MS-Off Ver
    2013
    Posts
    32

    Re: VBA to fill formula down till last row in column

    Hello Ionut,

    i am getting syntax error in this line of code .WorksheetFunction.Round( Range("H3:H" & lLR), 2)

    Regards
    Wither

  8. #8
    Registered User
    Join Date
    05-16-2016
    Location
    bangalore,India
    MS-Off Ver
    2013
    Posts
    32

    Re: VBA to fill formula down till last row in column

    Hi TMS,

    It is working Thank you !

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: VBA to fill formula down till last row in column

    You're welcome. Thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to drag the formula till the end of the data in previous column
    By winmaxservices1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-02-2015, 03:04 AM
  2. [SOLVED] vba to fill values till end of text values in B:B column.
    By johnodys in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-28-2014, 03:06 AM
  3. Sum till the column till the date match
    By ursanil in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-02-2014, 06:29 AM
  4. Help with Macro: I need to fill columns D-N only up till the row number in column A
    By zizou0178 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-19-2014, 02:16 PM
  5. [SOLVED] vba paste formula to end row till end of value in previous column
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-27-2013, 05:21 AM
  6. [SOLVED] Identify the last filled row in Column A and then drag formula in Column B till the same
    By bonny24tycoon in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-13-2012, 10:50 AM
  7. Fill array till first empty cell.
    By UncleDucc in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-05-2009, 09:56 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1