CODEWITHSUNDEEP
vbaexcelrow
Ievgen Sein
Ievgen Sein

Reputation: 3

Store a cell's value to a new row in a specific column every time that cell changes

In my nonworking example I use the cell C5 as the cell to enter a value. What I am trying to do is: Every time a new value is entered into the cell C5, that value should be stored in the column A starting from A1 and onwards.

(For example: I enter number 1.205 - it gets stored in A1, then I enter number 1.572 - it gets stored in A2, and so on).

I know that my method is probably far from correct, so any help is appreciated. I have tried a few different ways to achieve this, but I can't get it to work, maximum I have achieved is being able to store up until the 2nd row of column A. 

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim SourceCell As Range
    Dim CheckCell As Range
    Dim I As Integer

    I = 1
    Set SourceCell = Range("C5")
    Set CheckCell = Range("A" & I)

    If IsEmpty(CheckCell) Then
        CheckCell.Value = SourceCell         
    ElseIf Not IsEmpty(CheckCell) Then
        I = I + 1
        Set CheckCell = Range("A" & I)
        CheckCell.Value = SourceCell   
    End If
End Sub

Upvotes: 0

Views: 340

Answers (2)

Pᴇʜ
Pᴇʜ

Reputation: 57743

You just need to check if the changed cell is C5 and if so then find the last used cell in column A and write the value below:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$C$5" Then 'check if changed cell is C5
        Dim lRow As Long
        lRow = Range("A" & Cells.Rows.Count).End(xlUp).Row 'find last used row in column A

        Application.EnableEvents = False 'prevent triggering another change event
        Cells(lRow + 1, "A").Value = Target.Value 'write value in column A
        Application.EnableEvents = True
    End If
End Sub

Note that I added a Application.EnableEvents = False to prevent triggering another Change event by adding the value to column A.

Note: I recommend to always use Long instead of Integer especially when using it for row counting. Excel has more rows than Integer can handle.

Upvotes: 1

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim tgt As Range
    Set tgt = Range("C5")
    If Intersect(Target, tgt) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Cells(LastRow, 1) = Target.Value
    Application.EnableEvents = True    

End Sub

Function LastRow(Optional ByVal ws As Worksheet = ActiveSheet, Optional ByVal col As Variant = "A") As Long
    With ws
        LastRow = .Cells(.rows.Count, col).End(xlUp).Row
    End With
End Function

Upvotes: 0

Related Questions