visual basic increment on cell update

Currently trying to take each cell update and store in a cell that is basically A55 plus one row each update. So start with A55 then next update A56 then A57 so on forever on each update. Current code is stuck at cell 55 though:

Private Sub Worksheet_Calculate()
Dim target As Range
Set target = Range("U13")
Dim Counter As Integer
Counter = 55
If Not Intersect(target, Range("U13")) Is Nothing Then
Cells(Counter, "A").value = target
End If
Counter = Counter + 1
End Sub


Counter=55

Counter=Counter+1

MyCounter

Counter=Range("MyCounter").Value

Set Range("MyCounter").Value=Counter+1

3 Answers
3

Just count the rows in column A

Private Sub Worksheet_Calculate()
Dim target As Range, LstRw As Long

Set target = Range("U13")
LstRw = Cells(Rows.Count, "A").End(xlUp).Row + 1

If LstRw < 55 Then LstRw = 55

If Not Intersect(target, Range("U13")) Is Nothing Then
Cells(LstRw, "A").Value = target
End If

End Sub


xlUp

l

1

Not sure when you want to run init(), you'll have to pick an event. But each time you run Worksheet_Calculate() after that it will increment.

Public Counter As Integer

Sub Init()
Counter = 55
End Sub

Private Sub Worksheet_Calculate()
Dim target As Range
Set target = Range("U13")

If Not Intersect(target, Range("U13")) Is Nothing Then
Sheet1.Cells(Counter, "A").Value = target
End If
Counter = Counter + 1
End Sub


Create a helper cell to store your counter value and increment and update that cell's value before you exit.

If you use a named range for the cell, such as MyCounter, then modify your code to this:

MyCounter

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Counter As Integer

Counter = Range("MyCounter").Value

If Not Intersect(Target, Range("U13")) Is Nothing Then
Cells(Counter, 1).Value = Target
Range("MyCounter").Value = Counter + 1
End If

End Sub


I changed the event type to Worksheet_Change. Worksheet_Calculate doesn't allow you to pass the Target as Range.

Worksheet_Change

Worksheet_Calculate

Here is a screen shot

Cell Y1 is named "MyCounter" and you would initialize it to 55. Then each time you change the value of cell U13, it will put that value into the row that MyCounter is pointing to and then increment MyCounter.

I would recommend that you put your helper cell on another sheet instead of the way that I did it in the example, but if you do then update the code to include the sheet name in the range reference, such as Counter = Sheets("HelperSheet").Range("MyCounter").Value. Hope that helps.

Counter = Sheets("HelperSheet").Range("MyCounter").Value

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.