| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
"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 | |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
| If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ... | Excel Programming | |||
Linear Mode
