Hi,

I need to move the content of a cell by x number of columns (i.e. A1 to A4)
but I cannot have a formula in the destination cell. How can I do that easily?

thanks,

Hi Stephane

not sure what you mean here, do you want to
1) take the value (which is derived from a formula in A1) and put it in A4
i.e. you have in A1 =B1-B2 and this gives an answer of 5, now you want the 5
(but not the formula in A4)
if so, then copy (or cut) A1, click in A4 and choose edit / paste special -
values

2) do you want to put the same value in A4, which is in A1 so that if A1
changes the value in A4 also changes
e.g. in A1 you have 7, you want the 7 also to appear in A4. If you change
the A1 7 to 5, then A4 should change to 5.
if so, and you don't want a formula in A4 you will need to use a
worksheet_change event (ie VBA code) - please let us know if this is what
you're after and we'll come up with the code for you.

something else entirely, if so, could you please provide a few more details.

Cheers
JulieD

"Stephane" wrote in message
...
Hi,

I need to move the content of a cell by x number of columns (i.e. A1 to
A4)
but I cannot have a formula in the destination cell. How can I do that
easily?

thanks,

Hi Julie,

the second option is what I am looking for.

thanks,

"JulieD" wrote:

Hi Stephane

not sure what you mean here, do you want to
1) take the value (which is derived from a formula in A1) and put it in A4
i.e. you have in A1 =B1-B2 and this gives an answer of 5, now you want the 5
(but not the formula in A4)
if so, then copy (or cut) A1, click in A4 and choose edit / paste special -
values

2) do you want to put the same value in A4, which is in A1 so that if A1
changes the value in A4 also changes
e.g. in A1 you have 7, you want the 7 also to appear in A4. If you change
the A1 7 to 5, then A4 should change to 5.
if so, and you don't want a formula in A4 you will need to use a
worksheet_change event (ie VBA code) - please let us know if this is what
you're after and we'll come up with the code for you.

something else entirely, if so, could you please provide a few more details.

Cheers
JulieD

"Stephane" wrote in message
...
Hi,

I need to move the content of a cell by x number of columns (i.e. A1 to
A4)
but I cannot have a formula in the destination cell. How can I do that
easily?

thanks,

Hi Stephane

okay, right mouse click on the sheet tab of the sheet where you want this to
happen and choose
view code
in the white area on the right of the screen paste the following code in
----
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
Range("A4").Value = Range("A1").Value
End If

End Sub
---
now press the ALT & F11 keys to return to your workbook and test the code

Cheers
JulieD

"Stephane" wrote in message
...
Hi Julie,

the second option is what I am looking for.

thanks,

"JulieD" wrote:

Hi Stephane

not sure what you mean here, do you want to
1) take the value (which is derived from a formula in A1) and put it in
A4
i.e. you have in A1 =B1-B2 and this gives an answer of 5, now you want
the 5
(but not the formula in A4)
if so, then copy (or cut) A1, click in A4 and choose edit / paste
special -
values

2) do you want to put the same value in A4, which is in A1 so that if A1
changes the value in A4 also changes
e.g. in A1 you have 7, you want the 7 also to appear in A4. If you
change
the A1 7 to 5, then A4 should change to 5.
if so, and you don't want a formula in A4 you will need to use a
worksheet_change event (ie VBA code) - please let us know if this is what
you're after and we'll come up with the code for you.

something else entirely, if so, could you please provide a few more
details.

Cheers
JulieD

"Stephane" wrote in message
...
Hi,

I need to move the content of a cell by x number of columns (i.e. A1 to
A4)
but I cannot have a formula in the destination cell. How can I do that
easily?

thanks,

Hi Julie,

this seems to be exactly what I need but I have another twist for you:
imagine that I have to replicate this for a large number of cells.

thank you for you invaluable help!

"JulieD" wrote:

Hi Stephane

okay, right mouse click on the sheet tab of the sheet where you want this to
happen and choose
view code
in the white area on the right of the screen paste the following code in
----
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
Range("A4").Value = Range("A1").Value
End If

End Sub
---
now press the ALT & F11 keys to return to your workbook and test the code

Cheers
JulieD

"Stephane" wrote in message
...
Hi Julie,

the second option is what I am looking for.

thanks,

"JulieD" wrote:

Hi Stephane

not sure what you mean here, do you want to
1) take the value (which is derived from a formula in A1) and put it in
A4
i.e. you have in A1 =B1-B2 and this gives an answer of 5, now you want
the 5
(but not the formula in A4)
if so, then copy (or cut) A1, click in A4 and choose edit / paste
special -
values

2) do you want to put the same value in A4, which is in A1 so that if A1
changes the value in A4 also changes
e.g. in A1 you have 7, you want the 7 also to appear in A4. If you
change
the A1 7 to 5, then A4 should change to 5.
if so, and you don't want a formula in A4 you will need to use a
worksheet_change event (ie VBA code) - please let us know if this is what
you're after and we'll come up with the code for you.

something else entirely, if so, could you please provide a few more
details.

Cheers
JulieD

"Stephane" wrote in message
...
Hi,

I need to move the content of a cell by x number of columns (i.e. A1 to
A4)
but I cannot have a formula in the destination cell. How can I do that
easily?

thanks,

What is the large number of cells and where do you copy the value?

I'm guessing the cells are in column A and you always copy 3 columns to the
right (column D).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Target, Me.Range("a:a"))
On Error GoTo 0

If myRng Is Nothing Then Exit Sub

On Error Resume Next 'keep going through all the cells
Application.EnableEvents = False
For Each myCell In myRng.Cells
myCell.Offset(0, 3).Value = myCell.Value
'myCell.ClearContents 'wipe out the old value?
Next myCell
Application.EnableEvents = True
On Error GoTo 0

End Sub


Stephane wrote:

Hi Julie,

this seems to be exactly what I need but I have another twist for you:
imagine that I have to replicate this for a large number of cells.

thank you for you invaluable help!

"JulieD" wrote:

Hi Stephane

okay, right mouse click on the sheet tab of the sheet where you want this to
happen and choose
view code
in the white area on the right of the screen paste the following code in
----
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
Range("A4").Value = Range("A1").Value
End If

End Sub
---
now press the ALT & F11 keys to return to your workbook and test the code

Cheers
JulieD

"Stephane" wrote in message
...
Hi Julie,

the second option is what I am looking for.

thanks,

"JulieD" wrote:

Hi Stephane

not sure what you mean here, do you want to
1) take the value (which is derived from a formula in A1) and put it in
A4
i.e. you have in A1 =B1-B2 and this gives an answer of 5, now you want
the 5
(but not the formula in A4)
if so, then copy (or cut) A1, click in A4 and choose edit / paste
special -
values

2) do you want to put the same value in A4, which is in A1 so that if A1
changes the value in A4 also changes
e.g. in A1 you have 7, you want the 7 also to appear in A4. If you
change
the A1 7 to 5, then A4 should change to 5.
if so, and you don't want a formula in A4 you will need to use a
worksheet_change event (ie VBA code) - please let us know if this is what
you're after and we'll come up with the code for you.

something else entirely, if so, could you please provide a few more
details.

Cheers
JulieD

"Stephane" wrote in message
...
Hi,

I need to move the content of a cell by x number of columns (i.e. A1 to
A4)
but I cannot have a formula in the destination cell. How can I do that
easily?

thanks,







--

Dave Peterson

Stephanie,

You didn't tell all. Did you mean move, or copy? If the cell in A1 contains
a value (non-formula), you can move it to A4 by cutting/pasting, or more
easily be dragging it by it's border. If the cell contains a formula, and
you want the result of the formula in A4, you can do that by doing Copy,
then in A4 do Paste special - Values. Now delete A1. If neither is the
solution you need, perhaps you can describe what's going in in more detail.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Stephane" wrote in message
...
Hi,

I need to move the content of a cell by x number of columns (i.e. A1 to
A4)
but I cannot have a formula in the destination cell. How can I do that
easily?

thanks,