CODEWITHSUNDEEP
excelvba
Nash Husna
Nash Husna

Reputation: 45

how to subtract next cell from the previous cell

i want to subtract next cell but want to maintain 1st day data for the next day

i use

Sub Differences()
 With Range("B2", Range("B2").End(xlDown))
 .Offset(, 3) = Evaluate(.Address & "-" & .Offset(-1).Address)
 End With
End

but get this result which is incorrect

enter image description here

i want to get this results

enter image description here

Upvotes: 0

Views: 202

Answers (2)

joe
joe

Reputation: 41

This gets the intended results. I am assuming that the first row with data is A1, if it's not then adjust accordingly.

Sub test()
    
    Dim i As Long
    Dim lastRow As Long
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
   
    For i = 1 To lastRow
    
        If i = 1 Then
                Cells(i, 5) = Cells(i, 2)
        Else
                If Cells(i, 1) = Cells(i - 1, 1) Then
                    Cells(i, 5) = Cells(i, 2) - Cells(i - 1, 2)
                Else
                    Cells(i, 5) = Cells(i, 2)
                End If
        End If
    
    Next i
    
End Sub

Upvotes: 0

Christofer Weber
Christofer Weber

Reputation: 1474

Well you need to check the date somehow, if you want the date to be a part of the calculation.
There might be a nicer approach, but an easy one is to just loop the range and compare the date.

Sub Differences()
Dim vRng As Range, r As Variant, vDate As String
Set vRng = Range("B2", Range("B2").End(xlDown))
For Each r In vRng
    If r.Offset(, -1) = vDate Then
        r.Offset(, 3) = r - r.Offset(-1)
    Else
        r.Offset(, 3) = r
    End If
    vDate = r.Offset(, -1)
Next r
End Sub

Upvotes: 1

Related Questions