Cell Changes (A:A) will automatically add in column as historical data

[aliffrusli]

Hi expertise,
I need help on current VBA I'm working on right now.
Whenever I input in any of cell in "A:A" it will automatically register in B column.
If I overwrite value in A column for 2nd time, the new input data will be captured in B column and old data move to column C
As example I input 26/3/2023 in cell A2, B2 will be automatically captured 206/3/2023 as output.
And after that if I input new data 30/5/2023 in A2, B2 will reflect 30/5/2023 and C2 will reflect previous value as 26/3/2023.
The same logic applicable to any cell in A:A will inherit the same row column. Can anyone please help me? I already meet dead road. Thank you.

 

[James006]

Hi,
For the fun of it ... building input history on the same row ... you could test:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
Target.Offset(0, Cells(Target.Row, Columns.Count).End(xlToLeft).Column).Value = Target.Value
End Sub

 

[HaHoBe]

Hi James006,

And after that if I input new data 30/5/2023 in A2, B2 will reflect 30/5/2023 and C2 will reflect previous value as 26/3/2023.

Doesn't your code add the new value to the end?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
Target.Offset(0, 1).Insert xlToRight
Target.Offset(0, 1).Value = Target.Value
End Sub

Ciao,
Holger

 

[aliffrusli]

Hi James006,
This coding work brilliant!! Thanks. But if my Input cell now is no longer at Column A but shift to Column B, from which line I should change? Kindly help to advise :D

 

[aliffrusli]

Hi Holger,
Yes indeed the logic is meeting your coding. Thanks!!
But I have one concern if the input column is no longer in A column but change to D? From which line I should be able to revise it? Thanks!!!

 

[James006]

Glad you like it

Do you mean Column A AND Column B ... or do you mean replace Column A by Column B ????

To monitor where the event macro is operational, you need to adapt Target.Column ( For Column D use 4)

 

[HaHoBe]

Hi aliffrusli,

change the range to suit:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Column <> Range("D1").Column Then Exit Sub
Target.Offset(0, 1).Insert xlToRight
Target.Offset(0, 1).Value = Target.Value
End Sub

Holger

 

[James006]

Hi James006,

Doesn't your code add the new value to the end?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
Target.Offset(0, 1).Insert xlToRight
Target.Offset(0, 1).Value = Target.Value
End Sub

Ciao,
Holger

Hi Holger,

You are 100% right ... code does add value at the end of the row ...

I might be completely wrong ... but it is my understanding of what the OP initially requested ...

 

[HaHoBe]

Hi James006,

just take a look at the picture in the opening post: first column is Input, second is Current.

Holger

 

[James006]

OOPS ... did not look at the picture ...