little help on a formula subtraction

[Doghole]

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

 

[mart37]

Range("B2") = Val(TextBox1.Text)-Range("B3")

 

[Alex Blakenburg]

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.

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]

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.

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.

            .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]

Thanks so much I give this a crack tonight when I get home, very muchly apreciated.