+ Reply to Thread
Results 1 to 8 of 8

Paste formula to the last empty cell based on a non-empty cell on another column

  1. #1
    Valued Forum Contributor
    Join Date
    02-07-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2007, 2010
    Posts
    533

    Paste formula to the last empty cell based on a non-empty cell on another column

    Hi Everyone,

    I'm using this code to paste my formulas from Column I to M:

    Dim lastrow As Long
    lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row
    Range("I7615").Formula = "=IFERROR(VLOOKUP(A7615,NAMES,2,0),""XX, XXXX"")"
    Range("I7615:I" & lastrow).FillDown
    Range("I7615:I" & lastrow).Value = Range("I7615:I" & lastrow).Value
    Range("J7615").Formula = "=Text(B7615,""yyyy"")"
    Range("J7615:J" & lastrow).FillDown
    Range("J7615:J" & lastrow).Value = Range("J7615:J" & lastrow).Value
    Range("K7615").Formula = "=Text(B7615,""mmmm"")"
    Range("K7615:K" & lastrow).FillDown
    Range("K7615:K" & lastrow).Value = Range("K7615:K" & lastrow).Value
    Range("L7615").Formula = "=""Week""&"" ""&INT((11-WEEKDAY(B7615-1)+DAY(B7615))/7)"
    Range("L7615:L" & lastrow).FillDown
    Range("L7615:L" & lastrow).Value = Range("L7615:L" & lastrow).Value
    Range("M7615").Formula = "=Text(B7615,""dddd"")"
    Range("M7615:M" & lastrow).FillDown
    Range("M7615:M" & lastrow).Value = Range("M7615:M" & lastrow).Value

    it's working fine; however, I want the formula to be pasted on the next empty cell in the range.

    So essentially if A7800 is not empty and I7800 to M7800 is empty - then the formula would be pasted on these cells without touching i7615 to M7615 (start of range).

    Any help is appreciated.

    Thanks

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Paste formula to the last empty cell based on a non-empty cell on another column

    Maybe:

    Please Login or Register  to view this content.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Paste formula to the last empty cell based on a non-empty cell on another column

    BTW -

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

  4. #4
    Valued Forum Contributor
    Join Date
    02-07-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2007, 2010
    Posts
    533

    Re: Paste formula to the last empty cell based on a non-empty cell on another column

    Thank you but it doesn't seem to work.

    I have attached a file for your reference.

    What i would like to do is "paste" the formulas starting on Row 46 (columns I:M) and not overwrite the values from row 4 to 45.

    And if information is pasted on row 73 (column A:H) then the formulas should be pasted starting from row 73.

    I hope this could help.
    Attached Files Attached Files

  5. #5
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Paste formula to the last empty cell based on a non-empty cell on another column

    Well, if your formulas are correct (I can't test them here), then maybe:

    Please Login or Register  to view this content.

  6. #6
    Valued Forum Contributor
    Join Date
    02-07-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2007, 2010
    Posts
    533

    Re: Paste formula to the last empty cell based on a non-empty cell on another column

    Thank you but the formula does not work. I need to update and read the data on the row it was pasted on.

    Is LastRow a named range?

  7. #7
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Paste formula to the last empty cell based on a non-empty cell on another column

    Try this:

    Please Login or Register  to view this content.

  8. #8
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Paste formula to the last empty cell based on a non-empty cell on another column

    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. Paste Values empty cell, not empty
    By warston in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 12-23-2020, 08:16 PM
  2. Replies: 1
    Last Post: 05-26-2014, 01:31 PM
  3. Need to find empty filled cells in a column and copy to the empty cell below
    By Grahamfeeley in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-30-2014, 10:09 AM
  4. MACRO to empty a range of cells based on whether specified cell is empty
    By TBJV in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-01-2013, 10:35 PM
  5. Copy values from cell in active row, paste in first empty cell in spesified column.
    By Test123Test in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-19-2012, 05:20 PM

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