Make a number in a cell auto dissapear

%3CLINGO-SUB%20id%3D%22lingo-sub-1585849%22%20slang%3D%22en-US%22%3EMake%20a%20number%20in%20a%20cell%20auto%20dissapear%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1585849%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20to%20find%20an%20answer%20but%20cant%2C%20please%20help.%20What%20i%20cant%20work%20out%20is%2C%20I%20need%20to%20have%20two%20cells%20A%20and%20b%2C%20cell%20a%20i%20put%2050%20into%20and%20have%20cell%20b%20add%20it%2C%20once%20i%20hit%20enter%20i%20want%20i%20want%20cell%20a%20to%20go%20blank%20waiting%20for%20the%20next%20entry.%20So%20next%20i%20put%2050%20into%20cell%20a%20and%20then%20hit%20enter%20and%20have%20cell%20b%20show%20100%20while%20cell%20a%20goes%20blank%20again%20waiting%20for%20the%20next%20number.%20Any%20help%20would%20be%20really%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1585849%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1585904%22%20slang%3D%22en-US%22%3ERe%3A%20Make%20a%20number%20in%20a%20cell%20auto%20dissapear%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1585904%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F757971%22%20target%3D%22_blank%22%3E%40Peterpete_H%3C%2FA%3E%26nbsp%3BYou%20need%20to%20put%20a%20piece%20of%20code%20in%20the%20worksheet%20where%20you%20want%20this%20automatic%20sum%20and%20clear%20to%20occur.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Excel.Range)%0A%20%20%20%20%0AIf%20Target.Column%20%3D%201%20Then%0A%20%20%20ThisRow%20%3D%20Target.Row%0A%20%20%20Range(%22B%22%20%26amp%3B%20ThisRow).Value%20%3D%20Range(%22B%22%20%26amp%3B%20ThisRow).Value%20%2B%20Range(%22A%22%20%26amp%3BThisRow).Value%0A%20%20%20Range(%22A%22%20%26amp%3B%20ThisRow).Clear%0AEnd%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EOpen%20the%20Visual%20Basic%20editor%20and%20copy%20this%20code%20into%20the%20worksheet%20area.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202020-08-13%20at%2006.51.17.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F212169iB9D9E3BB58B8457D%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Screenshot%202020-08-13%20at%2006.51.17.png%22%20alt%3D%22Screenshot%202020-08-13%20at%2006.51.17.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1585948%22%20slang%3D%22en-US%22%3ERe%3A%20Make%20a%20number%20in%20a%20cell%20auto%20dissapear%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1585948%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3EThank%20you%20so%20very%20much%20am%20doing%20it%20now%2C%20i%20knew%20there%20had%20to%20be%20a%20way%20and%20im%20out%20of%20my%20depth%20but%20learning.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20again%2C%26nbsp%3B%20Peter%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1585954%22%20slang%3D%22en-US%22%3ERe%3A%20Make%20a%20number%20in%20a%20cell%20auto%20dissapear%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1585954%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F757971%22%20target%3D%22_blank%22%3E%40Peterpete_H%3C%2FA%3E%26nbsp%3BYou're%20welcome!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello all,

 

I have tried to find an answer but cant, please help. What i cant work out is, I need to have two cells A and b, cell a i put 50 into and have cell b add it, once i hit enter i want i want cell a to go blank waiting for the next entry. So next i put 50 into cell a and then hit enter and have cell b show 100 while cell a goes blank again waiting for the next number. Any help would be really appreciated.

3 Replies
Highlighted

@Peterpete_H You need to put a piece of code in the worksheet where you want this automatic sum and clear to occur.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    
If Target.Column = 1 Then
   ThisRow = Target.Row
   Range("B" & ThisRow).Value = Range("B" & ThisRow).Value + Range("A" &ThisRow).Value
   Range("A" & ThisRow).Clear
End If
End Sub

Open the Visual Basic editor and copy this code into the worksheet area.

Screenshot 2020-08-13 at 06.51.17.png

@Riny_van_EekelenThank you so very much am doing it now, i knew there had to be a way and im out of my depth but learning.

 

Thank you again,  Peter

Highlighted

@Peterpete_H You're welcome!