CODEWITHSUNDEEP
excelvba
Ram Sai A
Ram Sai A

Reputation: 1

i want to increment the number on a cell when date is updated on another cell in excel

i have date in A1 and number in B1, similarly date in D1 and number in E1. i want b1 and e1 to increment by 1 number whenever value changes in A1 and D1 respectively. i want the same logic to be applied to all the rows ex (if something changes in a2 then b2 should be incremented by 1 number, if something is updated in a3 then b3 should be be incremented by 1 number. similarly if d2 is updated then e2 should be incremented by 1 number and if d3 is updated then e3 should be incremented by 1 number.

i have tried using this VBA code 

Private Sub Worksheet_Change(ByVal Target As Range)
 If Not Intersect(Target, Range("A1")) Is Nothing Then
   Application.EnableEvents = False
   Range("B1").Value = Range("B1").Value + 1
   Application.EnableEvents = True
 End If
 If Not Intersect(Target, Range("D1")) Is Nothing Then
   Application.EnableEvents = False
   Range("E1").Value = Range("E1").Value + 1
   Application.EnableEvents = True
 End If
End Sub

but its only working for those particular cells when i try to put range in them, its not working.

Upvotes: 0

Views: 128

Answers (1)

Glenn G
Glenn G

Reputation: 667

I don't have excel available right now but try this

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim shAdd as string
        If Not Intersect(Target, Range("A:A")) Is Nothing Then
            Application.EnableEvents = False
            shAdd = "B" & Target.Row
            Range(shAdd).Value = Range(shAdd).Value + 1
            Application.EnableEvents = True
        End If
        If Not Intersect(Target, Range("D:D")) Is Nothing Then
            Application.EnableEvents = False
            shAdd = "E" & Target.Row
            Range(shAdd).Value = Range(shAdd).Value + 1
            Application.EnableEvents = True
        End If
End Sub

The reason it wasn't working for you is this line in your code Intersect(Target, Range("A1")) only refers to the cell "A1" and not the entire column. By replacing that with "A:A" I'm referencing the entire column. This checks to see if the Target was any cell in column A. Additionally, to make sure that the right cell is incremented on the same row as the changed cell, I've used the .Row attribute of the Target. Since your cell to increment will always be in column B or E, I just need to append the Target's row to the column letter and pass that as a string to the Range function here 

shAdd = "E" & Target.Row
Range(shAdd).Value = Range(shAdd).Value + 1

Upvotes: 1

Related Questions