View original (French)

VIA55 clear cell content when another is filled

Solved
kiko8111 Posted messages 176 Registration date   Status Member Last intervention   -  
via55 Posted messages 14393 Registration date   Status Member Last intervention   -
Hello, I created this example so that I can understand how to automatically erase the content of another:
In (B3:B7) I enter numbers. In this specific case, I would like that when I enter the number 5 in B4, the value in B3 (which is 7) is erased. The same goes for cells B5, B6, and B7.
Macro if needed
Thank you for your responses

https://www.cjoint.com/c/LDwlAxe1RRy

Configuration: Windows / Firefox 99.0

15 answers

  1.
    via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    By the way, I'm via55, my friend Raymond only intervened in post 2 ☺

    Your reduced file with the deletion macro:
    https://www.cjoint.com/c/LDxmLqq6pse

    Let me know if this is what you wanted

    Best regards
    Via

    --
    "Imagination is more important than knowledge."    A. Einstein
    1
  2. Le Pingou Posted messages 12275 Registration date   Status Contributor Last intervention   1 477
     
    Hello,
    And if we enter 5 in B3, what to do.....!
    How many numbers can be found in the range b3:b7?

    --
    Regards.
    The Penguin
    0
  3. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Hello

    What am I doing in this mess? Why is my nickname in your title??

    --
    "Imagination is more important than knowledge." A. Einstein
    0
  4. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Re,

    Raymond, hi by the way, having refreshed my memory, I looked into your problem
    If you want the value of the cell just above to be cleared when entering a value in a cell within the range, you need to go through a macro
    Macro to be placed in the worksheet of sheet1 (Open the VBA editor - double-click on the name of the sheet in the left tree structure - Paste the macro into the blank page) 
    Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B4:B7")) Is Nothing And Target.Value <> "" Then ' if an entry is made in the range B4:B7 and is a non-null value row = Target.Row ' row of the entry cell Range("B" & row - 1).ClearContents 'clears the cell in the row above End If End Sub

    Best regards
    Via

    --
    "Imagination is more important than knowledge." A. Einstein
    0
  6. kiko8111 Posted messages 176 Registration date   Status Member Last intervention   33
     
    Hi, I won't take long.
    Is there a way in column B that it deletes the number 7 entered in B3 when I enter the number 5 in B4 or similarly in the column?
    This would allow me to only have one cell to fill in for the month page I have in view and not worry about the other sheets?
    I know, I’m complicated, but like you, I like things to be practical and just.
    See you later, Christophe
    0
  7. kiko8111 Posted messages 176 Registration date   Status Member Last intervention   33
     
    Sorry, I forgot.
    If the same number is found in the other cells, it’s not too bothersome knowing that I named one cell as a reference because in my case I am using it as a direct calculation for the months.
    0
  8. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    You are not telling me what is wrong with my macro test?
    It correctly clears all values in B3 when a value is entered in B4 (regardless of the value)
    the same goes for B4 B5 then B5 B6 and finally B6 B7
    but maybe I didn’t understand what you want?
    I don’t understand what you mean by
    "This would allow me to have just one cell to fill for the month page that I have in mind and not worry about the other sheets?" (by the way, did you know that you can enter the same value in a cell across all sheets at once by first selecting all the sheets?)
    I also don't understand
    "If we find the same number in the other cells it’s not too troublesome knowing that I named a cell as a reference because in my case I’m in a pro forma for the month calculations "
    Do you mean by that that the macro should only clear if the number is different from that of the previous cell?

    --
    "Imagination is more important than knowledge." A. Einstein
    0
  9. kiko8111 Posted messages 176 Registration date   Status Member Last intervention   33
     
    Hello, I just made a workbook with the full month situation.
    Column A3 to A14 are different sheets by month.
    Column B3 to B14 are the months of the year in reverse numerical designation for the remaining calculation of my forecast.
    Column C14 is the reference for calculating the remaining months

    In the situation on the provided workbook, in C12 which is the last entered value, it needs to be totaled in C14 (this is correct in this case), but placing it back in the context of the actual workbook (months separated by pages), I would like to show that the last entered number appears, even if it does not correspond to the numerical value of the month (for example, 6 in column C7)

    I should have done this table from the beginning, but I searched before asking. That's why we find a calculation in column D that can be removed if needed.
    Hoping I have explained myself well
    Thank you, Christophe

    https://www.cjoint.com/c/LDxfgf8nMSy
    0
    1. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
       
      Re

      I still don’t understand what you’re trying to achieve!
      "In the provided spreadsheet, in C12, which is the last entered value, it needs to be totaled in C14 (in this case it’s fine), but when considering the context of the real spreadsheet (months separated by pages), I would like the last entered number to be displayed, even if it does not correspond to the numerical value of the month (for example, 6 in column C7)"
      You provided an example with only one sheet, while you say that your real spreadsheet has 12 pages; please provide the actual spreadsheet and explain more clearly what you want, and we might get somewhere

      Best regards
      Via
      0
  10. kiko8111 Posted messages 176 Registration date   Status Member Last intervention   33
     
    Hello Raymond, I replied this morning but I just realized that the last topic got pushed between the others.
    You need to check 9.
    Let me know if it went well please.
    Christophe
    0
  11. kiko8111 Posted messages 176 Registration date   Status Member Last intervention   33
     
    Re, I will simplify my request:
    I need that when I enter a numerical value from 1 to 12 in column B from B3 to B14, if there is a number within the specified range, it should be erased when another number is indicated.
    Christophe
    0
  12. kiko8111 Posted messages 176 Registration date   Status Member Last intervention   33
     
    Re, Thanks via. Sorry for the name, I think Excel made me lose a bit of clarity.
    As for the file, it’s perfect.
    Promise, next assistance (if needed) the title will remain anonymous.
    Best wishes, Christophe
    0
  13. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Well, it's good that you found your happiness
    It's okay about the title ☺ I was just a bit surprised, usually when someone wants to make sure to get in touch with me, they send me a private message (by clicking on my username in a discussion)
    Take care and maybe see you next time

    --
    "Imagination is more important than knowledge." A. Einstein
    0
  14. kiko8111 Posted messages 176 Registration date   Status Member Last intervention   33
     
    Re, I'm back.

    I have my numbers organized in the same column but one on each sheet.
    I am stuck.
    Is it possible to modify the formula: Range("B" & ligne - 1).ClearContents

    At the beginning, I entered the cells separated by a comma.
    General Worksheet Change

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E40,E76,E112,E148,E184,E220,E256,E292,E328,E364,E400,E439")) Is Nothing And Target.Value <> "" Then ' if entry in the range B4:B7 and a non-null value
    ligne = Target.Row ' row of the entered cell

    Thank you, Christophe
    0
  15. via55 Posted messages 14393 Registration date   Status Member Last intervention   2 759
     
    Re

    You seem to be on the first macro that didn't satisfy you since it only clears the line above and not the one from my post 13!!!
    And in your actual file, the cells seem to be scattered, I'm afraid you might be making a mess....
    Anyway, since you have several sheets, you must have a macro for each sheet.
    Of course, Range("B" & line - 1).ClearContents is modifiable, but what would you replace it with?
    If you enter a value in E112, should the values in E40, E76, etc. be cleared?
    You would do well to provide me with the actual file so we can adapt the macro instead of messing around!!

    --
    "Imagination is more important than knowledge." A. Einstein
    0
  16. kiko8111 Posted messages 176 Registration date   Status Member Last intervention   33
     
    Re, sorry Raymond but this is the only way I found to have a chance that you reply to me. I have very fond memories of your explanations, the program I set up works wonderfully.
    I don't know if you kept a page, to locate yourself in the last one (12) I had certain data we never discussed. This is used to make a forecast both on the unapproved amounts and those approved while being able to reduce the month (very practical). The only drawback is the need to go back to page 12 to make a change.
    I will test your macro and keep you informed.
    Thanks, Christophe
    -1