Automatic updating of values sequentially

[sol95]

Hi all,

It is first time posting excel question to the board.
I am desperated right now.

I am trying to work out the below.
Row
Coloum Value A
Coloum Value B
Coloum Value C

When I input the new value in value A, then value A to be updated at Value B automaticlly and value B to be updated at Value C automatically.

In other words, as I updated new value in the first coloum, all the rest of value to be move one down keeping its value.

Does it make sense and is that possible?

Can anyone please tell me how to do this?

Thanks!!!!!

 

[pgc01]

Hi sol95

Paste this code in the worksheet's module (Right-clink on the worksheet's tab and choose View code).

Each time you enter a value in A1 the cells in column A are pushed down.

Hope this helps
PGC

Dim A1Value

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
    A1Value = Range("A1")
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
    Range("A2").Insert (xlShiftDown)
    Range("A2").Value = A1Value
End If
End Sub

 

[sol95]

It works!!

Hi pgc01,

It works!!!
you are awesome!
Thanks you so much!!!!

 

[sol95]

bit more problem

Hi pgc 01,

it worked as i mentioned prevously but as I am going to input value different cell from A1 I have changed cell number with my deginated cell number as shown below and it doesn't work and shows some error message in the second line.

Could you help me more?
how can I change this code to other cell range?

Dim C4Value

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$C$4" Then
C4Value = Range("C4")
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$4" Then
Range("C5").Insert (xlShiftDown)
Range("C5").Value = C4Value
End If
End Sub

 

[pgc01]

Hi

I just pasted your code in a worksheet's module and it worked just fine.

What is the error you get? which second line?

Remark: This is a worksheet event. You always have to write in code on the worksheet module. If you write the code on the worksheet module Sheet2 it will not work in Sheet1.

Kind regards
PGC

 

[sol95]

Hi pgc 01,

Thanks for the prompt reply.
I know it only applies to the one worksheet which have code is given.

when i tried with my changed code, this error comes up.


compile error
sub or functin not defined

what am i doing wrong?
I did exactly as you instructed with my changed version.
but when i paste mine the error message shows up.

 

[pgc01]

Ho sol95

I'm sorry but I cannot reproduce your error.

I've just created a new workbook, pasted the modified code you posted in a worksheet module and it worked with no problem.

I suggest you also create a new workbook and paste the code from your post. This may give you a hint about what is happening.

Hint: did you in the meanwhile edited other vba code in another module? Sometimes the vba compiler complains if there is an error elsewhere.

Hope this helps
PGC

 

[sol95]

Hi pgc 01,

It works now.
I was testing in many different worksheets and it must be the problem.
I have deleted everything else and it works now.

again Thank you so much for your advice and help.
It really saved my big problem.

Have a good day!!!!!

 

[pgc01]

I'm glad it all works now!

Cheers
PGC