Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jun 2008
    Location
    Watford UK
    Posts
    104
    Thanks
    1
    Thanked 1 Time in 1 Post

    a blank cell result in Excel

    How do I get Excel 2007 to make the result of an if query a blank?

    I know I can use if(a1="a","yes",""), but this does not produce a true blank. If in another part of the spreadsheet I do a calculation on the result cell, I get a 'value' error. If I check a cell with a double quote result with an 'isblank' query it return FALSE.

    I also know I can use if(a1="a","yes",0), but I don't want to fill the spreadsheet with unnecessary zeros and I can't use conditional formatting to hide the zeroes as some are needed to display.

    Anyone got any ideas

    David

  2. #2
    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
    You cannot return a true blank. If you don't want to return 0, you have to use "" and then adjust your other formulas to cater for the cell having "" in it.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    I know I can use if(a1="a","yes",""), but this does not produce a true blank.
    David,

    Try placing the following code into the sheet module. If a change is made to cell A1 and that value is anything other than "a", a null value will be placed into cell B1.

    HTH,
    Maud

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
        If Not Intersect(Target, Range("A1")) Is Nothing Then
            [b1] = IIf([a1] = "a", "yes", vbNullString)
        End If
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2013-10-10 at 22:15.

Posting Permissions

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