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

Copper Contributor

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.

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

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)
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)

@NikolinoDE  @Sergei Baklan 

 

That's excellent, thank you very much. I appreciate the quick responses.