moveing values with macros

[carlos1973]

hi,

is there a way of using a macro to move a value from one cell to another. then the next time it is run adding the value in the ref cell to the next cell down the reference cells value is a variable . for example

ref cell = c1

no run 1st run 2nd run 3rd run 4th run
c1 12 12 13 7 27
a1 0 12 12 12 12
a2 0 0 13 13 13
a3 0 0 0 7 7
a4 0 0 0 0 27

im sure there must be a solution that is really simple but i dont have much experience with excel and i would appreciate your help

 

[Vasant Nanavati]

If I understand the problem correctly, something like the following
(untested) should work:

Sub AddRefCell()
If Range("A1") = "" Then
Range("A1") = Range("C1")
Else
Range("A" & Rows.Count).End(xlUp).Offset(1) = Range("C1")
End If
End Sub

I assume the value of C1 will be changed manually between runs.

--

Vasant

hi,

is there a way of using a macro to move a value from one cell to another.

then the next time it is run adding the value in the ref cell to the next
cell down the reference cells value is a variable . for example

ref cell = c1

no run 1st run 2nd run 3rd run 4th run
c1 12 12 13 7 27
a1 0 12 12 12 12
a2 0 0 13 13 13
a3 0 0 0 7 7
a4 0 0 0 0 27

im sure there must be a solution that is really simple but i dont have

much experience with excel and i would appreciate your help

 

[Don Guillett]

try this.Right click sheet tab>view code>copy\paste this. Works in row 1. If
you want for cell c1 all the time, you need to modify Cells(x, ac) = Target
to Cells(x, ac) = range("c1")

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row <> 1 And Target.Column < 2 Then Exit Sub
Application.EnableEvents = False
ac = ActiveCell.Column
x = Cells(Rows.Count, ac - 1).End(xlUp).Row + 1
Range(Cells(2, ac - 1), Cells(x, ac - 1)).Copy Cells(2, ac)
Cells(x, ac) = Target
Application.EnableEvents = True
End Sub

--
Don Guillett
SalesAid Software
[email protected]

hi,

is there a way of using a macro to move a value from one cell to another.

then the next time it is run adding the value in the ref cell to the next
cell down the reference cells value is a variable . for example

ref cell = c1

no run 1st run 2nd run 3rd run 4th run
c1 12 12 13 7 27
a1 0 12 12 12 12
a2 0 0 13 13 13
a3 0 0 0 7 7
a4 0 0 0 0 27

im sure there must be a solution that is really simple but i dont have

much experience with excel and i would appreciate your help

 

[Don Guillett]

I re-read and what you wanted is really a lot more simple

Private Sub Worksheet_change(ByVal Target As Range)
If Target.Address <> "$C$1" Then Exit Sub
Cells(Cells(Rows.Count, "a").End(xlUp).Row + 1, "a") = Target
End Sub

 

[carlos1973]

thanks guys sheet works great now

If I understand the problem correctly, something like the following
(untested) should work:

Sub AddRefCell()
If Range("A1") = "" Then
Range("A1") = Range("C1")
Else
Range("A" & Rows.Count).End(xlUp).Offset(1) = Range("C1")
End If
End Sub

I assume the value of C1 will be changed manually between runs.

--

Vasant



then the next time it is run adding the value in the ref cell to the next
cell down the reference cells value is a variable . for example
much experience with excel and i would appreciate your help