Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Paste Value and Link to Another Cell
Hello,
I am trying to write what I thought would be a simple macro but I am having some difficulty in implementing it. In essensence I have 3 cells. A1 is a formula A2 Needs to convert the value from A1 into its Value (Paste Special - Value) function A3 Needs to equal A2 after A2 has been converted to a value. When I record this macro, I cannot seem to get A2 to stay at the pasted value and I get lengthy iterations.(I am using the macro to break a circular reference but still keep some fluidness) Below is the macro I am having difficulties with. At this point, I would like this to run only once per execution, however I may add a loop later on. Any suggestions would be greatly appreciated. Thank you for your time. Sub N2T() Range("B81").Select ActiveCell.FormulaR1C1 = "=R[-1]C" Range("B81").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("B85").Select ActiveCell.FormulaR1C1 = "=R[-4]C" Range("B82").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Paste Value and Link to Another Cell
Sub N2T() Range("B81").Value = Range("B80").Value Range("B85").FormulaR1C1 = "=R[-4]C" Range("B82").Select End Sub -- There are 10 types of people in the world - those who understand binary and those who don't. "cardan" wrote: Hello, I am trying to write what I thought would be a simple macro but I am having some difficulty in implementing it. In essensence I have 3 cells. A1 is a formula A2 Needs to convert the value from A1 into its Value (Paste Special - Value) function A3 Needs to equal A2 after A2 has been converted to a value. When I record this macro, I cannot seem to get A2 to stay at the pasted value and I get lengthy iterations.(I am using the macro to break a circular reference but still keep some fluidness) Below is the macro I am having difficulties with. At this point, I would like this to run only once per execution, however I may add a loop later on. Any suggestions would be greatly appreciated. Thank you for your time. Sub N2T() Range("B81").Select ActiveCell.FormulaR1C1 = "=R[-1]C" Range("B81").Select Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("B85").Select ActiveCell.FormulaR1C1 = "=R[-4]C" Range("B82").Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Paste Value and Link to Another Cell
On May 28, 8:10*pm, Geoff wrote:
Sub N2T() * * Range("B81").Value = Range("B80").Value * * Range("B85").FormulaR1C1 = "=R[-4]C" * * Range("B82").Select End Sub -- There are 10 types of people in the world - those who understand binary and those who don't. "cardan" wrote: Hello, I am trying to write what I thought would be a simple macro but I am having some difficulty in implementing it. In essensence I have 3 cells. A1 is a formula A2 Needs to convert the value from A1 into its Value (Paste Special - Value) function A3 Needs to equal A2 after A2 has been converted to a value. When I record this macro, I cannot seem to get A2 to stay at the pasted value and I get lengthy iterations.(I am using the macro to break a circular reference but still keep some fluidness) Below is the macro I am having difficulties with. At this point, I would like this to run only once per execution, however I may add a loop later on. *Any suggestions would be greatly appreciated. Thank you for your time. Sub N2T() * * Range("B81").Select * * ActiveCell.FormulaR1C1 = "=R[-1]C" * * Range("B81").Select * * Selection.Copy * * Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ * * * * :=False, Transpose:=False * * Application.CutCopyMode = False * * Range("B85").Select * * ActiveCell.FormulaR1C1 = "=R[-4]C" * * Range("B82").Select End Sub- Hide quoted text - - Show quoted text - I just put it in my model. Works like a charm! Thank you!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PASTE LINK option not available when I select PASTE SPECIAL to link an image in Excel to a Word document. | Links and Linking in Excel | |||
To paste link or to =cell ref?? | New Users to Excel | |||
Paste active link with a macro | Excel Programming | |||
Macro: Copy and Paste Boomberg Link | Excel Discussion (Misc queries) | |||
Paste Link enters a 0 into the cell where I paste. How do I elemin | Excel Discussion (Misc queries) |