"Mopy"
 

I am looking for a way to to select a range of cells with formula's and then
to MOPY it to another place. Result : two ranges with exaclty the same
formula's whether the cell adresses used in the formula's are relative or
absolute. In fact a bit of copy and a bit of move.

Regards,

Henk

"Mopy"
 

Hi Henk

If I've understood your question correctly, you want 2 identical ranges...
Can you not just set the second range equal the first?

e.g. it data is in A1, the formula in B1 would be "=A1" (without quotes)

Trevor

"Henk" wrote:

I am looking for a way to to select a range of cells with formula's and then
to MOPY it to another place. Result : two ranges with exaclty the same
formula's whether the cell adresses used in the formula's are relative or
absolute. In fact a bit of copy and a bit of move.

Regards,

Henk

"Mopy"
 

Trevor,

I am afraid that is not the solution. What I need is two ranges with axactly
the same formula's. So copy for instance formula =(a1*(a2+b7)) from cell B5to
cell AX2256 should result in the fomula =(a1*(a2+b7)) in cell AX2256. So,
the same result as if you move the cell, but leving the original in place
(cell B5).

For your information : I often have to copy very complicated formula's like
this and then change them slightly afterwards. For instance, changing the
name of a named range only in formula's like

=VLOOKUP(A63,SearchDatabaseXXX,6,False),
=VLOOKUP(B63,SearchDatabaseXXX,6,False),
=VLOOKUP(C63,SearchDatabaseXXX,6,False) etecetera

change to

=VLOOKUP(A63,SearchDatabaseYYY,6,False),
=VLOOKUP(B63,SearchDatabaseYYY,6,False),
=VLOOKUP(C63,SearchDatabaseYYY,6,False)

Regards,

Henk

"Trevor Williams" wrote:

Hi Henk

If I've understood your question correctly, you want 2 identical ranges...
Can you not just set the second range equal the first?

e.g. it data is in A1, the formula in B1 would be "=A1" (without quotes)

Trevor

"Henk" wrote:

I am looking for a way to to select a range of cells with formula's and then
to MOPY it to another place. Result : two ranges with exaclty the same
formula's whether the cell adresses used in the formula's are relative or
absolute. In fact a bit of copy and a bit of move.

Regards,

Henk

"Mopy"
 

Henk wrote:
Trevor,

I am afraid that is not the solution. What I need is two ranges with axactly
the same formula's. So copy for instance formula =(a1*(a2+b7)) from cell B5to
cell AX2256 should result in the fomula =(a1*(a2+b7)) in cell AX2256. So,
the same result as if you move the cell, but leving the original in place
(cell B5).

For your information : I often have to copy very complicated formula's like
this and then change them slightly afterwards. For instance, changing the
name of a named range only in formula's like

=VLOOKUP(A63,SearchDatabaseXXX,6,False),

How about doing a FIND = REPLACE '=
do the copy
then on both ranges do a FIND '= REPLACE =

ie replace all the equals signs (=) with apostrophe& = ('=)

then get rid of the apostrophe.


=VLOOKUP(B63,SearchDatabaseXXX,6,False),
=VLOOKUP(C63,SearchDatabaseXXX,6,False) etecetera

change to

=VLOOKUP(A63,SearchDatabaseYYY,6,False),
=VLOOKUP(B63,SearchDatabaseYYY,6,False),
=VLOOKUP(C63,SearchDatabaseYYY,6,False)

Regards,

Henk

Hi Henk

[quoted text clipped - 13 lines]

Henk

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200802/1

"Mopy"
 

Hi Henk

Try This - it asks you for the range you want to copy and the start cell of
where you want to copy the formulae to.

Sub CopyFormulae()
Dim myCopyRng As String, myDestRng As String
Dim cell As Range
myCopyRng = InputBox("Enter the range that you want to copy." & _
"e.g. A1:A20", "Copy Range")
If myCopyRng = "" Then Exit Sub
myDestRng = InputBox("Enter the start cell where you want to paste to." & _
"e.g. B1", "Paste Range")
If myDestRng = "" Then Exit Sub
x = 0
For Each cell In Range(myCopyRng)
Range(myDestRng).Offset(x, 0).Formula = cell.Formula
x = x + 1
Next
End Sub

"Henk" wrote:

Trevor,

I am afraid that is not the solution. What I need is two ranges with axactly
the same formula's. So copy for instance formula =(a1*(a2+b7)) from cell B5to
cell AX2256 should result in the fomula =(a1*(a2+b7)) in cell AX2256. So,
the same result as if you move the cell, but leving the original in place
(cell B5).

For your information : I often have to copy very complicated formula's like
this and then change them slightly afterwards. For instance, changing the
name of a named range only in formula's like

=VLOOKUP(A63,SearchDatabaseXXX,6,False),
=VLOOKUP(B63,SearchDatabaseXXX,6,False),
=VLOOKUP(C63,SearchDatabaseXXX,6,False) etecetera

change to

=VLOOKUP(A63,SearchDatabaseYYY,6,False),
=VLOOKUP(B63,SearchDatabaseYYY,6,False),
=VLOOKUP(C63,SearchDatabaseYYY,6,False)

Regards,

Henk

"Trevor Williams" wrote:

Hi Henk

If I've understood your question correctly, you want 2 identical ranges...
Can you not just set the second range equal the first?

e.g. it data is in A1, the formula in B1 would be "=A1" (without quotes)

Trevor

"Henk" wrote:

I am looking for a way to to select a range of cells with formula's and then
to MOPY it to another place. Result : two ranges with exaclty the same
formula's whether the cell adresses used in the formula's are relative or
absolute. In fact a bit of copy and a bit of move.

Regards,

Henk

"Mopy"
 

Via a macro, you could just assign the formulas of one range to another
range, for example...

Range("C1:C3").Formula = Range("A1:A3").Formula

Rick


"Henk" wrote in message
...
Trevor,

I am afraid that is not the solution. What I need is two ranges with
axactly
the same formula's. So copy for instance formula =(a1*(a2+b7)) from cell
B5to
cell AX2256 should result in the fomula =(a1*(a2+b7)) in cell AX2256. So,
the same result as if you move the cell, but leving the original in place
(cell B5).

For your information : I often have to copy very complicated formula's
like
this and then change them slightly afterwards. For instance, changing the
name of a named range only in formula's like

=VLOOKUP(A63,SearchDatabaseXXX,6,False),
=VLOOKUP(B63,SearchDatabaseXXX,6,False),
=VLOOKUP(C63,SearchDatabaseXXX,6,False) etecetera

change to

=VLOOKUP(A63,SearchDatabaseYYY,6,False),
=VLOOKUP(B63,SearchDatabaseYYY,6,False),
=VLOOKUP(C63,SearchDatabaseYYY,6,False)

Regards,

Henk

"Trevor Williams" wrote:

Hi Henk

If I've understood your question correctly, you want 2 identical
ranges...
Can you not just set the second range equal the first?

e.g. it data is in A1, the formula in B1 would be "=A1" (without quotes)

Trevor

"Henk" wrote:

I am looking for a way to to select a range of cells with formula's and
then
to MOPY it to another place. Result : two ranges with exaclty the same
formula's whether the cell adresses used in the formula's are relative
or
absolute. In fact a bit of copy and a bit of move.

Regards,

Henk

"Mopy"
 

What I need is two ranges with axactly the same formula's.

I don't think that's what you really want. What you really want is
references outside the range to be copied to be literal but references
within the range to be copied to be relative. Don't you agree (upon
reflection)?

--
Jim
"Henk" wrote in message
...
| Trevor,
|
| I am afraid that is not the solution. What I need is two ranges with
axactly
| the same formula's. So copy for instance formula =(a1*(a2+b7)) from cell
B5to
| cell AX2256 should result in the fomula =(a1*(a2+b7)) in cell AX2256. So,
| the same result as if you move the cell, but leving the original in place
| (cell B5).
|
| For your information : I often have to copy very complicated formula's
like
| this and then change them slightly afterwards. For instance, changing the
| name of a named range only in formula's like
|
| =VLOOKUP(A63,SearchDatabaseXXX,6,False),
| =VLOOKUP(B63,SearchDatabaseXXX,6,False),
| =VLOOKUP(C63,SearchDatabaseXXX,6,False) etecetera
|
| change to
|
| =VLOOKUP(A63,SearchDatabaseYYY,6,False),
| =VLOOKUP(B63,SearchDatabaseYYY,6,False),
| =VLOOKUP(C63,SearchDatabaseYYY,6,False)
|
| Regards,
|
| Henk
|
| "Trevor Williams" wrote:
|
| Hi Henk
|
| If I've understood your question correctly, you want 2 identical
ranges...
| Can you not just set the second range equal the first?
|
| e.g. it data is in A1, the formula in B1 would be "=A1" (without quotes)
|
| Trevor
|
| "Henk" wrote:
|
| I am looking for a way to to select a range of cells with formula's
and then
| to MOPY it to another place. Result : two ranges with exaclty the same
| formula's whether the cell adresses used in the formula's are relative
or
| absolute. In fact a bit of copy and a bit of move.
|
| Regards,
|
| Henk
|