little help on a formula subtraction

Doghole

New Member
Joined
Jul 2, 2019
Messages
5
Hi,

I have a program that runs as soon as you open it it asks to enter the details, then it loads it onto the spreadsheet moving the other cells down 1. What I need now and it is doing my head in haha. When I enter the new details which will always be greater than the last ones I have entered I need it to automatically subtract it from it.

What I am doing is entering the power usage, so when it appears on the spreadsheet it shows the amount used since the last recording not the total amount.
I have tried all sorts of things but I know it's a simple fix but its just one of them things.


Private Sub CommandButton1_Click()
If Range("a2") <> "" Then
Rows("2:2").Select
Selection.Insert shift:=xlDown
End If

If Range("a2") = "" Then
Range("a2") = TextBox1.Text
Range("B2") = TextBox2.Text
Range("C2") = TextBox3.Text
Range("D2") = TextBox4.Text
End If

TextBox1.Text = Format(Now(), "dd-mmm-yy")
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
Range("c1").Select
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Private Sub Label2_Click()

End Sub

Private Sub Label3_Click()

End Sub

Private Sub TextBox1_Change()

End Sub

Private Sub TextBox2_Change()

End Sub

Private Sub UserForm_Initialize()
TextBox1.Text = Format(Now(), "dd-mmm-yy")
End Sub
 

Attachments

  • report.jpg
    report.jpg
    164 KB · Views: 5
  • report 2.png
    report 2.png
    177.4 KB · Views: 5

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,096
Office Version
  1. 2016
Platform
  1. Windows
Range("B2") = Val(TextBox1.Text)-Range("B3")
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,692
Office Version
  1. 365
Platform
  1. Windows
As I understand it each time you are entering the actual current meter readings and you only want the spreadsheet to show the movement since the last meter reading.

I personally would keep 3 additional columns for the actual meter reading you have entered so you have an audit trail of the actual values entered but using your current layout, see if the below code works for you. (replace your Private Sub CommandButton1_Click() with this code)

Since each row only contains the latest movement, the very first row you enter into the spreadsheet will need to be the Cumulative meter reading at the start or the sum of the usage will not equal you latest meter reading.

PS: I didn't have text boxes to test it with so you may need to use Val around the Text Box as shown by mart37.

VBA Code:
Private Sub CommandButton1_Click()

    '--- additional parameters ---------------------------
    Dim sht As Worksheet
    Dim LastRow As Long
 
    Set sht = ActiveSheet
    '------------------------------------------------------
 
    If Range("a2") <> "" Then
        Rows("2:2").Select
        Selection.Insert shift:=xlDown
    End If
 
    If Range("a2") = "" Then
        '--- modified code -------------------------------
        LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
        With sht
            .Range("a2") = TextBox1.Text
            .Range("B2") = TextBox2.Text - Application.WorksheetFunction.Sum(.Range("B3", "B" & LastRow))
            .Range("C2") = TextBox3.Text - Application.WorksheetFunction.Sum(.Range("C3", "C" & LastRow))
            .Range("D2") = TextBox4.Text - Application.WorksheetFunction.Sum(.Range("C3", "C" & LastRow))
        End With
    '------------------------------------------------------
    End If
 
    TextBox1.Text = Format(Now(), "dd-mmm-yy")
    TextBox2.Text = ""
    TextBox3.Text = ""
    TextBox4.Text = ""
    Range("c1").Select
End Sub
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,692
Office Version
  1. 365
Platform
  1. Windows
As I understand it each time you are entering the actual current meter readings and you only want the spreadsheet to show the movement since the last meter reading.

I personally would keep 3 additional columns for the actual meter reading you have entered so you have an audit trail of the actual values entered but using your current layout, see if the below code works for you. (replace your Private Sub CommandButton1_Click() with this code)

Since each row only contains the latest movement, the very first row you enter into the spreadsheet will need to be the Cumulative meter reading at the start or the sum of the usage will not equal you latest meter reading.

PS: I didn't have text boxes to test it with so you may need to use Val around the Text Box as shown by mart37.

VBA Code:
Private Sub CommandButton1_Click()

    '--- additional parameters ---------------------------
    Dim sht As Worksheet
    Dim LastRow As Long

    Set sht = ActiveSheet
    '------------------------------------------------------

    If Range("a2") <> "" Then
        Rows("2:2").Select
        Selection.Insert shift:=xlDown
    End If

    If Range("a2") = "" Then
        '--- modified code -------------------------------
        LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
        With sht
            .Range("a2") = TextBox1.Text
            .Range("B2") = TextBox2.Text - Application.WorksheetFunction.Sum(.Range("B3", "B" & LastRow))
            .Range("C2") = TextBox3.Text - Application.WorksheetFunction.Sum(.Range("C3", "C" & LastRow))
            .Range("D2") = TextBox4.Text - Application.WorksheetFunction.Sum(.Range("C3", "C" & LastRow))
        End With
    '------------------------------------------------------
    End If

    TextBox1.Text = Format(Now(), "dd-mmm-yy")
    TextBox2.Text = ""
    TextBox3.Text = ""
    TextBox4.Text = ""
    Range("c1").Select
End Sub

You might want to check that your column A really is a date, if not you will need to change the "A2" line to the below.

VBA Code:
            .Range("a2") = CDate(TextBox1.Text)
)

To check if its really a date either use =isnumber(a2) which you want to be TRUE or =istext(a2) which you want to be FALSE.
 

Doghole

New Member
Joined
Jul 2, 2019
Messages
5
Thanks so much I give this a crack tonight when I get home, very muchly apreciated.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,488
Messages
5,770,384
Members
425,614
Latest member
rhobbo1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top