Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    populate empty cell if condition is true (XL2000 s

    I'd like to keep a cell empty (no values, text, formulae, nothing) until a condition is met. Is it possible to write a formula to do this sort of thing:

    =if(A1="","",set empty cell A2 to "hello world")

    I think I could write a UDF to do this but I'd prefer to do it with XL's built in functions.

    stuck

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: populate empty cell if condition is true (XL2000 s

    Formulas cannot change the value of other cells directly. Since you don't want to have a formula in the cell, a user-defined function wouldn't help either. You can use the Worksheet_Change event:
    - Right-click the sheet tab.
    - Select View Code from the popup menu.
    - Copy the following code into the module:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
    If Not Range("A1") = "" Then
    Application.EnableEvents = False
    Range("A2") = "Hello World"
    Application.EnableEvents = True
    'Else
    'Range("A2").ClearContents
    End If
    End If
    End Sub

    The two lines that have been commented out are optional - they clear A2 when A1 is blank.

  3. #3
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: populate empty cell if condition is true (XL20

    Thanks, I didn't think it was possible without code but I did want to check that.

    Thanks too for the code, you've enabled me to do what I asked but I see now that's not what I really meant! I can use a third cell so it becomes much easier, along the lines of...
    in cell A3, use =if(A1="","",UDF(text to put in A2))
    UDF
    Range("A2").value = "text to put in A2"
    end UDF

    Ken

  4. #4
    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

    Re: populate empty cell if condition is true (XL20

    As Hans mentioned, that will not work. A function that is used on a worksheet cannot assign a value to another cell in that way. You need a subroutine (or function) which can be triggered by a worksheet event.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: populate empty cell if condition is true (XL20

    <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

  6. #6
    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

    Re: populate empty cell if condition is true (XL20

    As a matter of interest, why do you need to keep the cell empty?
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: populate empty cell if condition is true (XL20

    I have a workbook with a filter db on the first page, this acts as a mail-merge data source for a Word doc, this works fine. I now have an occasional requirement to extend the filter db in certain circumstances.

    By having a blank column between the extra fields and the usual ones they are out side the 'current region' and thus ignored by various macros in th workbook. When the condition requiring the extra fields was met I wanted to fill in the empty cell so the two regions became one and were no longer ignored by the macros.

    I have more or less solved my problem now by making something else critical in the workbook fail unless the user presses a button, the button runs a sub that fills in the gap and then does the critical calculation. It's not a problem that the workbook is crippled until the user intervenes, in fact it helps them to feel in control.

    Ken

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: populate empty cell if condition is true (XL20

    Why not have the macro when it is run, check the condition, and adjust the range accordingly at run time?

    Steve

  9. #9
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: populate empty cell if condition is true (XL20

    To add the check to the existing macros would disrupt the logical flow of the users interaction with the workbook. Creating a new macro that just adds a pause to the flow of the users work while checking for the condition and the adjusting as necessary will work better. As I said, the extra step of having to press a button helps the users to feel they are controling things.

    Ken

Posting Permissions

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