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

aliffrusli

New Member
Joined
Mar 20, 2023
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Attachments

  • Sample1.jpg
    Sample1.jpg
    12.2 KB · Views: 7

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Hi,
For the fun of it ... building input history on the same row ... you could test:
VBA Code:
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
 
Upvote 0
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?

VBA Code:
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
 
Upvote 0
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
 
Upvote 0
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!!!
 
Upvote 0
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)
 
Upvote 0
Hi aliffrusli,

change the range to suit:

VBA Code:
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
 
Upvote 0
Solution
Hi James006,

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

VBA Code:
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 ...
 
Upvote 0
Hi James006,

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

Holger
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top