+ Reply to Thread
Results 1 to 5 of 5

Replacing Empty Cell With Formula

  1. #1
    Registered User
    Join Date
    05-03-2019
    Location
    San Francisco
    MS-Off Ver
    7
    Posts
    28

    Replacing Empty Cell With Formula

    Hello,

    I have a macro where I want to replace all empty cells in a range with a formula. Here is the code that I have currently:

    Please Login or Register  to view this content.
    However, this replacement method removes the relative cell referencing. I need "G14" in the IF statement to update to whichever row the empty cell is in. For example, if the empty cell is K20, I need the replacement formula to be this:

    Please Login or Register  to view this content.
    Cheers!

  2. #2
    Registered User
    Join Date
    10-26-2021
    Location
    Canada
    MS-Off Ver
    2016
    Posts
    5

    Re: Replacing Empty Cell With Formula

    I think you could solve your problem by changing your formula a bit. Instead of saying "G14" you could say "ADDRESS(ROW(),7,1,1)" and i think that might solve it.
    Last edited by AGuardian101; 12-22-2021 at 04:37 PM.

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Replacing Empty Cell With Formula

    Probably the ADDRESS function shall be nested in INDIRECT so it would be rather complicated. (and it's volatile, so will be fully recalculated each time anything changes in a sheet)
    Why not simply write the formula to all empty cells instead of replacing their content? The R1C1 notation would make this easy. Try:

    Please Login or Register  to view this content.
    of course code is longer, but the formula in the worksheet would be just regular formula
    Best Regards,

    Kaper

  4. #4
    Registered User
    Join Date
    05-03-2019
    Location
    San Francisco
    MS-Off Ver
    7
    Posts
    28

    Re: Replacing Empty Cell With Formula

    Thank you Kaper! I need to look into R1C1 notation and "on error" stuff to make sure I understand this 100%, but so far it looks to work well.

  5. #5
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Replacing Empty Cell With Formula

    This both On Error instructions switch of and on standard reaction to the execution time error.
    Why there could be an error? If case of no blank cells in a range replace was doing 0 replacements ;-) but assigning a no-cell range to a range type variable would cause an error.

    You can observe (and learn) row number column number notation also in a spreadsheet. In excel options/formulas you will find something like "use R1C1 adressing style"

+ 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. [SOLVED] Replacing a cell in a formula by an other
    By WINGSWINTHECUP in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-15-2016, 08:31 AM
  2. [SOLVED] Paste formula to the last empty cell based on a non-empty cell on another column
    By bhenlee in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-02-2015, 04:07 PM
  3. Replacing cell value without replacing formula in the cell
    By kingoftheace in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-17-2012, 06:28 AM
  4. Replacing a formula with another cell
    By help000 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-20-2008, 11:25 AM
  5. Replacing #VALUE! error with an empty cell
    By Breezey in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-08-2007, 10:58 AM
  6. Replacing data using the cell value not the formula
    By Hunter1978 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-09-2006, 05:20 AM
  7. Replacing empty cells with '-'
    By Kazuki in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-26-2006, 06:20 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