Forum Discussion

Zaac_Abrahams's avatar
Zaac_Abrahams
Copper Contributor
Sep 21, 2020

Function that automatically clears a cell once its numerical value is added to another cell.

Hi,

 

This might be a silly question, but I was wondering if there was an Excel function that allows me to input a numerical value into one cell, have that value added to another cell, and then have the first cell clear itself, so that once the first value is cleared the change to the second value remains.

 

Thanks, Zaac.

excel
Excel for web

4 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Sep 21, 2020
    This could be done with VBA. However, as far as I know, Excel Web cannot run VBA like Excel 2016.

    Work with VBA macros in Excel for the web
    https://support.microsoft.com/en-gb/office/work-with-vba-macros-in-excel-for-the-web-98784ad0-898c-43aa-a1da-4f0fb5014343?ui=en-us&rs=en-gb&ad=gb

    Maybe someone else has a better idea and can help you more than I can. Wish you a pleasant day / night.

    I would be happy to know if I could help.
    Nikolino
    I know I don't know anything (Socrates)
    • NikolinoDE's avatar
      NikolinoDE
      Platinum Contributor
      Sep 21, 2020
      Here is a small approach if you want to do it with VBA under Excel 2016/2019, etc.

      Private Sub Worksheet_Change(ByVal Target As Range)
      Dim rngc As Range, tmp As String
      Application.EnableEvents = False
      If Not Intersect(Target, Range("B2")) Is Nothing Then
      Set rngc = Range("A:A").Find(What:=Target.Value, lookat:=xlWhole)
      If Not rngc Is Nothing Then
      tmp = Target.Value
      Target.Clear
      Rows(rngc.Row).Delete
      'or if the found line should not be deleted: rngc.Clear
      Else
      MsgBox tmp & vbLf & "not in column A available!"
      End If
      End If
      Application.EnableEvents = True
      End Sub

      Nikolino
      I know I don't know anything (Socrates)

Resources