subtract a cell from another cell using vba code

john zal 0
Viewed 43

In my VBA code the goal is to subtract from cell d1 the amount in cell b5 and display it in b6. The function should be called when the button in cell a5 is pressed on. What my code does right is not working. I dont have a lot of experience in VBA code so I do not know what I am doing. I have attached a photo.

Sub UserForm_Subtract()
   Worksheets(1).Range("B6").Value = "D1"-"B5"
End Sub

sample

range subtraction vba cell excel
1 Answers

Ways to Reference worksheet:

  1. Codename: Sheet1.Range("A1").value (usually best because sheet codenames don't generally change)
  2. Worksheet Name: Worksheets("somesheetname").Range("A1").value (not the worst, but if people change the name of the sheet it will disconnect.)
  3. Worksheet Index: Worksheets(1).Range("A1").value (index of worksheet based on order displayed in workbook; easy to break if sheets are rearranged)
  4. ActiveSheet: ActiveSheet.Range("A1").value (references whatever sheet is active/foreground at time of execution)

Then there's tons of ways to reference ranges as well (depending on how many times you're iterating, how long your lines are, what purpose it's for)...
All of the following examples will do what you're asking:

Sub Preform_Subtraction_Verion1()
    
    Dim CalcWS As Worksheet
    Dim StartingTotal As Double
    Dim DayTotal As Double
    
    Set CalcWS = ThisWorkbook.Worksheets("Sheet1")
    
    With CalcWS
        StartingTotal = .Range("D1").Value
        DayTotal = .Range("B5").Value
        .Range("D7").Value = StartingTotal - DayTotal
    End With
    
End Sub 
'}-------------------------------------------------------------------------
Sub Preform_Subtraction_Verion2()

    Sheet1.Range("D7").Value = Sheet1.Range("D1").Value - Sheet1.Range("B5").Value
    
End Sub 
'}-------------------------------------------------------------------------
Sub Preform_Subtraction_Verion3()

    With Sheet1
        .Range("D7").Value = .Range("D1").Value - .Range("B5").Value
    End With
    
End Sub 
'}-------------------------------------------------------------------------
Sub Preform_Subtraction_Verion4()

    With Sheet1
        .[D7] = .[D1] - .[B5]
    End With
    
End Sub
Cameron Critchlow0
Related