Forum Discussion

Eqqsalad's avatar
Eqqsalad
Copper Contributor
Jul 21, 2023

Using VBA to add one cell to a second cell and then clear the initial cell

Hello,

 

My goal here is to have one input cell (D9) have its value added to a second cell that will keep a running sum of all numbers input in D9, this total cell will be C7. I'm only a little familiar with VBA, a little as in I can vaguely understand the code but most of my work has been stitching together fragments of other people's code or very basic work with case and if statements. I will have other code in this sheet that depends on the worksheet change event as well.

 

Here is the code fragment I've been using to try and achieve this goal. Am I close, or am I completely off target? Regardless, does anyone have a solution? 

Private Sub Worksheet_Change(ByVal Target As Range)
    With ActiveSheet
        Set xpInput = .Range("D9").Value
        Set xpTotal = .Range("C7").Value
    
        Dim xpInput As Range, xpOutput As Range

            If Not xpInput Is Nothing Then
                Set xpOutput = xpOutput + xpInput
                If Not xpInput Is Nothing Then
                .Range("D9").Clear
                End If
            End If
    End With
End Sub

 

 

excel
Macros and VBA

2 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Jul 21, 2023

    Eqqsalad 

    My only change world be to use defined names rather than direct cell references to reduce the likelihood of the code being broken whenever an end user rearranges the worksheet or inserts rows.

    Option Explicit

Sub Test3()
    Dim xpInput As Range, xpTotal As Range
    Set xpInput = Range("Input")
    Set xpTotal = Range("Total")
    xpTotal.Value = xpTotal.Value + xpInput.Value
    xpInput.ClearContents
End Sub

    Then again, I dislike the traditional practice of direct cell referencing in principle, so perhaps I am not the best judge.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 21, 2023

    Eqqsalad 

    You should declare variables before you use them.

    You should use variables consistently - you use xpTotal and xpOutput for the same cell.

    The keyword Set is used to assign an object variable such as a Range, not to set a number value such as Range("C7").Value.

    You could use code like this:

    Sub Test2()
    Dim xpInput As Range, xpTotal As Range
    Set xpInput = Range("D9")
    Set xpTotal = Range("C7")
    xpTotal.Value = xpTotal.Value + xpInput.Value
    xpInput.ClearContents
End Sub

     

Resources