• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA or formula to calculate and update cells

Hello,
I copy and paste into monthly columns Trial Balance(TB) from Accounting system. Number in TB are shown on accumulated basis.
For example, if I paste numbers for February, they represent January+February. So, in order to get February numbers, I have to go manually through 400 rows and deduct hard coded(pasted) February-January.

Can we build a formula or VBA (preferred), when I copy and paste a new month, VBA can work and adjust all numbers accordingly? Thank you,
JanuaryFebruaryFebruary Should be
February= February- January
Expense 1
8​
20​
12
Expense 2
7​
30​
23
Expense 4
6​
24​
18
Expense 300
9​
28​
19
 
Here a possibility
To be recorded in the sheet where data is
Code:
Option Explicit

Sub UpdateMonth()
Dim LC As Integer
Dim WkRg  As Range, Rg  As Range
    Set WkRg = ActiveSheet.UsedRange
    For Each Rg In WkRg.Columns(WkRg.Columns.Count).Cells
        If (Application.IsNumber(Rg)) Then Rg = Rg - Rg(1, 0)
    Next Rg
End Sub
 
Back
Top