+ Reply to Thread
Results 1 to 4 of 4

Replacing "0" values with null string in array while preserving cell formula

  1. #1
    Valued Forum Contributor AlvaroSiza's Avatar
    Join Date
    09-19-2007
    Location
    Staffordshire
    MS-Off Ver
    2007
    Posts
    591

    Replacing "0" values with null string in array while preserving cell formula

    Hola gurus,

    I have a number of nested formulas that are becoming somewhat laborious. I now realize that I need even one more added to each part in order to return a null string should the lookup value be equal to zero.

    I am requesting assistance in what, on the surface to me logically seems like a scan of my array for zeros, whereby they would then be null (blank), but without erasing the formulas from the cell.

    I would like to add this additional functionallity to some code I have already written in which my presentation (we'll call it that) is formatted, which is initiated by a click event.

    I would assume that that the format would hide the unused rows (my code already) and then go through each cell in a specified array and nullify any zero values, but without erasing the formula contents of the cell (preserving the formula for future use whereby that cell might not be zero).

    I really, really appreciate any insight or thoughts.

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    Couple of ways

    Tools > Options > View Tab > hide zero's

    or

    condtiontal format

    http://www.contextures.com/xlCondFormat01.html

    or format

    General;;

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Valued Forum Contributor AlvaroSiza's Avatar
    Join Date
    09-19-2007
    Location
    Staffordshire
    MS-Off Ver
    2007
    Posts
    591

    Thanks Noob

    Holy easy! As is typically the case I was attempting to do far too much.

    Thanks again

  4. #4
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    No problem

    Glad it helped

    The other option is a if statement but I assumed you didn't want to edit 100's of formulas

    VBA Noob

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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