Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Mar 2012
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts

    How to change the content of an Excel active cell based on a condition

    I have an excel work sheet where certain condition is tested when an input is performed on an active cell. If the condition tests as true, the content of the active cell should be returned to null. I have tried to use an excel formula to assign '=""' to the active cell but it holds that you cannot assign a value to a cell reference. i.e. to say =AC1="".

    The result of the above would alsways be false, with the active cell, because excel would think you are trying to compare the content of the referenced cell to the null status, which would result in false.

    I would be glad if someone helps out. Anybody?

    Might be a VBA solution.

    Thank you.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,192
    Thanks
    14
    Thanked 325 Times in 319 Posts
    You will need a macro to change a cell without a formula, but you need to provide more explicit requirements for someone to create that code.

    A non-VBA solution is to use an intermediate cell to enter the text and have a formula read that value, test the criteria and either use the value entered or an alternate value. Then have formulas refer to this formula instead of the cell directly entered. You can use cond formatting to cross-out the entry if it is not used to give the user a visual clue.

    Steve

  4. #3
    New Lounger
    Join Date
    Mar 2012
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Ok. Thank you again steve. Here is the real scenario:

    There is a column range on my spreadsheet, say C2:C15. Within this range, when i type any letter between 'A' and 'E' on any of the cells within the range, there should be a cell counting the number of times the letter appears within the range. i.e. immdiatedly I Type the letter 'A' in a cell, "the activecell", it should be counted as '1' A in another cell, say 'D3'.

    I also have another table that shows a fixed number of each letter that is expected to feature within the range (C2:C15) specified above.

    $Benchmark$
    A - 2
    B - 3
    C - 5
    D - 3
    E - 1

    When a letter, say 'C', is typed severally, on each occasion 'D3' shows the number of times the letter 'C' appears within the C2:C15 range. Lets say another cell 'E3' shows the corresponding benchmark value for the number of 'C's enter within the range C2:C15. If 'C' is entered 6 times within the range, D3=6; on the benchmark table, 'C' should ordinarily appear 5 times, hence, E3=5.

    When D3=6 and E3 = 5; then, this should trigger the VBA action. i.e.

    IF D3>E3 Then

    activecell.value=""

    End If

    I understand that VBA Macros DOESNT normally take results of excel formula as input...that only a VBA function can return an outcome for an excel formular to use and not vise-versa.

    I hope this is clear enough. This explanation has been done to the best of my ability.
    Last edited by bola; 2012-04-04 at 07:41. Reason: ....I understand that VBA Macros DOESNT normally

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,902
    Thanks
    190
    Thanked 719 Times in 655 Posts
    Bola,

    Here's some VBA that I think does what you want. Sorry I probablly didn't faithfully follow your layout but I'm sure you can adapt the code. Note: the code is in the Sheet1 module as the Worksheet_Change event needs to be there. Also note the Cells C3:C15 are named MyEntries!

    The worksheet is attached but here's the code for those who may not want to download the sheet.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    '*** Limiting the Worksheet_Change event to a firing when a single cell is changed
    
       Dim isect As Range
       Dim iOffset As Integer
       
       Application.EnableEvents = False   'Turn off events to prevent endless loop!
       
       Set isect = Application.Intersect(Range("MyEntries"), Target)
       If isect Is Nothing Then
    '    MsgBox "Ranges do not intersect"
       Else
    '     MsgBox "Changed", vbOKOnly + vbInformation, "Cell Changed"
         If Target.Value <> UCase(Target.Value) Then Target.Value = UCase(Target.Value)
         iOffset = Asc(Target.Value) - 64
         If [I1].Offset(iOffset, 0) > [H1].Offset(iOffset, 0) Then Target.Value = ""
       End If
    
       Application.EnableEvents = True    'Turn events back on for next time
       
    End Sub
    Hope this helps.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  6. #5
    New Lounger
    Join Date
    Mar 2012
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Whao! Ok, now, this worked! Basically.

    Again, the BIG picture would be to have it work if the "MyEntries" range got autofiltered at any point in time; which means we would at that point want to work with the visible rows that remains available from the filter; "The COUNTIF function might not work" here...it doesnt ever work with autofiltered recordsets.

    Thank you.

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,902
    Thanks
    190
    Thanked 719 Times in 655 Posts
    Bola,

    Try this one. Check out the SumProduct formula
    =SUMPRODUCT(SUBTOTAL(3,OFFSET(MyEntries,ROW(MyEntr ies)-MIN(ROW(MyEntries)),,1))*(MyEntries=$G2))
    for the Counts column. I wish I could but I can't take credit for this amazing formula...found it via Google!

    I moved the MyEntries range down so the filter wouldn't interfere with the Benchmarks. These would probably be better off on another sheet out of the way!
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  8. #7
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 167 Times in 163 Posts
    Hi RG

    That is a nice formula for effectively 'sub-counting' visible cells!

    However, it isn't clear to me why Bola would want this 'feature' to apply when filtering data, since if you then 'unfilter' the data the 'rules' are 'broken'.
    Also, if you change the 'benchmark' allowed number of entries after entering data, again, the 'rules' are easily broken.

    It seems to me that perhaps the best way would be to use a combination of data validation and conditional formatting to achieve a reasonable compromise.
    Also, this means you don't need any VBA.

    See attached file.

    zeddy
    Attached Files Attached Files

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,902
    Thanks
    190
    Thanked 719 Times in 655 Posts
    Zeddy,

    I couldn't agree more! However, I gave up long ago trying to figure out what posters were trying to accomplish since they rarely post enough information to figure it out, and just try to answer their question. Then refine the answer when they come back with more information as in this case. With apologies to Tennyson: "Mine is not to reason why merely to code and recode ...!"
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  10. #9
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 167 Times in 163 Posts
    Hi RG

    Yes. You are right.
    Give them what they ask!

    But did you like the data validation method rather than VBA?

    zeddy

  11. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,902
    Thanks
    190
    Thanked 719 Times in 655 Posts
    Zeddy,

    Yes, it's quite slick. Not to nit pick, Ok so I'm nit picking, it doesn't blank the cell like the OP asked. Actually, that's one of the problems I've always had with DV it can get "USERS" locked in a loop {input - message - input - message} and they can't figure how to get out. I know they should know how, read the error message, to get out but it's sort of like most tech support where if it's not in the script you can't go there!
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  12. The Following User Says Thank You to RetiredGeek For This Useful Post:

    zeddy (2012-04-06)

  13. #11
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,474
    Thanks
    22
    Thanked 167 Times in 163 Posts
    Hi RG

    I like whacky requests and for me half the fun is trying to figure out what they are really doing.
    And then thinking about how I might use the various posted solutions in the future.

    ..Now technically data validation does blank the cell as long as the Cancel button is clicked to acknowledge that what is entered is not allowed. But I agree that data validation without proper (or any) description of what is not allowed can be very very frustrating and bordering on useless.
    But data validation can be very useful and is relatively easy to implement.
    For example, it is nice to have messages like "Expense claims must be less than $5,000" or "You cannot enter a date more than two weeks ago" or "You can only pick a selection from the list" etc. Better that, rather than a mysterious "I entered something and now it's gone".
    Of course, data validation can also easily be bypassed (by copy-and-paste etc).
    (You can use the 'circle invalid entries' feature from the auditing toolbar of course, but I prefer to also use conditional formatting as a more reliable method of showing invalid values immediately as they arise).

    Now, back to the OP requirement:
    When I looked at the early posting (before the filter issue was raised), I was able to use the Custom option within data validation to put a True/False formula in (e.g. for cell [C6] ):
    =(COUNTIF(inputBlock,C6)<=VLOOKUP(C6,block1,2,FALS E))
    ..where block1 was a two column named range for the benchmarks,
    and inputBlock refers to $C$2:$C$15
    So we didn't need to have a Counts column for the values A,B,C,D,E.
    This was fine. No VBA required.

    Then, to accomodate the subsequent requirement for use when data was filtered, I tried to incorporate your stunning formula directly within the data validation True/False formula. For example, using:
    =SUMPRODUCT(SUBTOTAL(3,OFFSET(inputBlock,ROW(input Block)-MIN(ROW(inputBlock)),,1))*(inputBlock=C6))<=VLOOKU P(C6,block1,2,FALSE)
    But I just couldn't get it to work directly within the data validation.
    It wasn't because the formula is too long (I believe the max formula length allowed in data validation is something like 254 chars).
    I even tried to use a 'named' formula, but couldn't seem to get it.

    So I ended up using the extra column (as in my posted worksheet) to calc the filtered counts (using your formula), and then referred to these in my data validation formula via:
    =VLOOKUP(C6,block2,3,FALSE)<=VLOOKUP(C6,block2,2,F ALSE)
    ..where block2 referred to the extended range.

    So I was hoping someone would come up with a better data validation formula that didn't require the extra calc column.

    Many thanks for your comments.
    And a thank you for finding that stunning formula.

    zeddy

  14. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    5,902
    Thanks
    190
    Thanked 719 Times in 655 Posts
    Zeddy,

    Right you are! I also like to see different solutions as they always get me thinking. That's one of the things I love about the lounge there are so many different experience sets and mind sets that there is always something new and interesting popping up. It keeps my crusty old brain from atrophing, at least I hope so.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


Tags for this Thread

Posting Permissions

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