Clear individual ranges of cells if different individual cells change. I need nested IF changes.

  • I attempted to find a solution in this forum and online, but being out of the loop for so long, my Excel skills are a little rusty.


    I am working on a spreadsheet where if I enter a value in a certain cell, in this case, B3, I want cells R3:T5 to be cleared since if any of the cells in a unique range have data in them, the value in another cell value gets a #VALUE Error due to a different calculation. I have various 3x3 ranges of cells that DO NOT have formulas in them, but whose values are used in other formulas that get the #VALUE Error if there are any values in those cells if I can't clear that 3x3 set of cells. The workbook only has 2 sheets presently, but all the VBA will be on the first sheet. Due to the size of the workbook, it is difficult to attach the file.


    I believe I need to write a VBA event using a Worksheet_Change(ByVal Target As Range), but don't know how to use it correctly and eventually need to nest the IF statements in it.


    I was thinking something like this would work, but it doesn't. I actually need 81 nested ElseIf Statements in it where the ... is.

    Obviously, once I can get the first 2 If statements to work, I can write the other 79!


    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "B3" Then

    Range("R3:T5").ClearContents

    ElseIf Target.Address = "C3" Then

    Range("U3:W5").ClearContents

    ...

    End If

    End Sub


    Any help would be greatly appreciated.If I am totally on the wrong track, I take no offense. I've been out of the loop for a while!

  • Hello and Welcome to the Forum :)


    At this stage, one has to guess the structure of your worksheet ...;)


    Given you have indicated ... does it mean for cell D3 that the range X3: Z5 needs to be deleted ???


    If that is the case ... how can you have 82 different cases ... since the max should 17 ... i.e. Column Q ....;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • If you are dealing with 81 cases ... it could mean all your input cells are located in range B3:J11


    If that is the case ...could you confirm that for cell B11 .. your corresponding range is R27:T29 ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Carim,


    Actually, I have a 9x9 section of the worksheet basically a Soduku board. Each of those 81 cells initially is blank. Associated with each of those cells, I have a cell that has a listing of the numbers that have not been used in that Square, Row, or Column. The values listed in that associated cell, I have a calculated formula that makes up the numbers that are still possible in that cell based on what numbers have already been used. In a different section of the sheet. I have 81 3x3 cells that one can place numbers in to remove them from the list of numbers that are possible for that particular cell. If there are any numbers in that particular 3x3 cell range, it throws the #VALUE Error in the associated cell.


    Since there are 81 cells I "may" enter a value in, I want to make sure the distinct corresponding 3x3 set of cells is cleared so as to NOT interfere with the formula. In theory, I have to have an entry in the VBA for each cell B3:J11.


    Does that clear it up? I have attached a screenshot.


    Actually, the 81 cells are all MERGED Cells, but do act as single cells.

  • Have you had a chance to read the message # 3 ...??? just above your latest post ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Based on my interpretation ... you could test following code



    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Carim,


    I appreciate the effort, but due to the fact that my cells aren't contiguous, a looping version of the code won't work. There are cells in between the cells I need to be evaluated for being changed. You may notice in my screenshot of the sheet that the cell I initially referred to as B3 is actually a Merged Cell of B3-B5. B7 is actually a Merged Cell of B7-B9 and so on.


    The cells to be checked for change are B3-J3 all the way through B35-J35 with skips of 3 rows for each row.

    Each of these cells corresponds to Clearing R3:T5 through AR35:AT37


    I'm not expecting anyone to write the entire VBA event(s) just a headstart on how to proceed.


    I guess to make it a little easier to understand without loading the entire spreadsheet (which is only 2 sheets, 1 that I have attached the screenshot of, and the other one that has a little over 6000 calculations, is that in essence, if someone can help me write the VBA so that if I change B3, R3:T5 would get their values Cleared.

    THEN

    The VBA code so that if I change another unique CELL, let's say R35 then another set of cells (let's say AR35:AT37) would have their values Cleared


    What I need to know or figure out, is IF I need to write 1 gigantic VBA Event with 81 distinct IF/ELSEIF statements in it (if that is possible) or if there is a way to write 81 distinct VBA "events" where each one will act upon a different Cell and the 3x3 set of cells I need to be cleared? I know I can't have 2 Worksheet_Change events in the same sheet.


    Before I started this forum, I actually had it working for 1 cell and clearing out the other 3x3 without an issue. When I tried to write it as an ElseIf, it blew up and I could never get it back to even working for 1 cell change. With AutoSave on, it took forever to test, so I shut it off and lost my coding.


    Thank you.

  • You are welcome


    In my humble opinion, within Excel ...MERGED Cells should be totally FORBIDDEN ..:cursing::evil:


    They are inspired by Word ... which is the Worst piece of software ever ... and they generate potential GIGANTIC problems... for Zero benefit ... since, when it comes to aesthetic considerations, Excel, contrary to Word ..., can adjust both the height and width of any cell ...


    Cheers

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • I understand your objections to Merged Cells. Although it may NOT be the best way of doing things, I did have a little different approach than Daniel Ferry did. His Excel sheet is a little more advanced in that if you are familiar with solving difficult Sodukus he has the ability to directly show items like "Paired Doubles, and the various ways of looking for digits that can be eliminated from consideration for a cell.


    I needed to create a way to have that second box with 9 boxes for each cell in the Soduku puzzle.


    If it is NOT possible to clear a 3x3 section of contiguous cells, I will understand that. I am only looking for a way to do that if possible.

    I have to believe that if I can do that for 1 cell and its corresponding 3x3 range, it should be possible to do that for a series of them, either 1 by 1 or as a large VBA series of events.


    With literally days of formula creation invested, it would be difficult for me to change my outlook on a solution. If I had the general method I could use to do what I envison, even if it were for 2 distinct cell changes without being in a LOOP, I could look into changing the setup of the design to use ALL contiguous cells.


    Thank you again for your help.

  • You are welcome :)


    To stick to the initial issue of coding an event macro to clear corresponding ranges , could you attach your sample sheet ...


    Cannot promise you there is a solution ... but I can give it a try ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Thanks :)


    Will take a look at how to handle your question ...DESPITE...the Merged Cells :S

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Before getting in the Clearing process ...


    Could you test the following event (which only spits out a message) to check if the Corresponding Ranges are in line ...


    Code
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column > 10 Then Exit Sub
    If Target.Count <> 3 Then Exit Sub
    Dim i As Integer, j As Integer, y As Integer
     i = Target.Row: j = Target.Column
     ' Determine Corresponding Coordinates
     y = ((Int(j - 2) * 3) + 18) + (Int(Int(j - 2) / 3))
    MsgBox "     Corresponding Start Target Row is " & i & "  " & vbNewLine & vbNewLine & _
    " and Corresponding Start Target Column is  " & y
    End Sub


    Hope this will help

    :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Glad to hear this hurdle is now behind ... :)


    Thanks a lot for your Thanks ... AND for the Like :thumbup:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • For the sake of completeness ...


    below is the initial Change Event macro you were looking for :

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column > 10 Then Exit Sub
    If Target.Count <> 3 Then Exit Sub
    Dim i As Integer, j As Integer, y As Integer
     i = Target.Row: j = Target.Column
     ' Determine Corresponding Column Number
     y = ((Int(j - 2) * 3) + 18) + (Int(Int(j - 2) / 3))
    ' When User deletes the Merged Cell ... Contents of Corresponding Range gets cleared '''''''
     If Cells(i, j).MergeArea.Cells(1, 1).Value = "" Then Cells(i, y).Resize(3, 3).ClearContents
    End Sub

    Hope this will help


    All the Best for your Project :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • That doesn't clear any cells. When I change the contents of the cell, none of the 3x3 range get cleared and I continue to get the #VALUE Error in the cell above the changed cell.


    The spreadsheet I sent should work and do what I said it was doing.

    If you want to experiment, place the numbers 3, 4, 5, 6,7, 8 in any of the Cells in Box 1 (Top Left hand corner of Main Soduku Box)

    Above Cell B3, in Cell B2 you will see 1 2 9. If anywhere in Cells R3-T5 you put in any of those 3 numbers, you will see those corresponding numbers disappear from Cell B2. Put in the numbers 2 & 9 somewhere in R3-T5 in separate boxes. Cell B2 now only shows "1". If you then enter the Number 1 in cell B3, the 2&9 do NOT get cleared, and you get the #VALUE Error in Cell B2.


    Your initial set of code identified the correct Row and Column for the top-left cell in the range of cells that needed to be cleared, but this current code doesn't do anything to solve the issue.


    I'm not sure what can be incorrect.

    Sorry.

  • Well ... kind of strange ... since it is working fine at my end ... 8)


    Have you used : Application.EnableEvents = False ... ?


    If so ... events have to be re-instated with Application.EnableEvents = True


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!