Automatic updating of values sequentially

sol95

New Member
Joined
Oct 10, 2006
Messages
19
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!!!!!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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

Code:
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

New Member
Joined
Oct 10, 2006
Messages
19
It works!!

Hi pgc01,

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

sol95

New Member
Joined
Oct 10, 2006
Messages
19
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

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

New Member
Joined
Oct 10, 2006
Messages
19
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

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

New Member
Joined
Oct 10, 2006
Messages
19
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!!!!! :biggrin:
 

Forum statistics

Threads
1,136,421
Messages
5,675,759
Members
419,585
Latest member
popsin

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
Top