Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
refer to a cells value in a macro
Is it possible to refer to the cell address generated within a cell to
specify a location to paste. The macro below pastes all the data to cell (S27). I need the data to be pasted to the Value in cell (S27) not the cell itself. The formula already in this cell generates a cell address, currently ($s$28) and this will be changed by the user each time the macro is ran. Do I need to enter the word value somewhere??? Sub Macro3() ' ' Macro3 Macro ' Macro recorded 24.02.2005 ' ' Range("C20:O22").Select Selection.COPY Range("S27").Select Selection.PasteSpecial PASTE:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("N11").Select Application.CutCopyMode = False End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
refer to a cells value in a macro
You cannot have a formula and a value in the same cell. Once you paste a
value in, the formula is lost. -- HTH RP (remove nothere from the email address if mailing direct) "loulou" wrote in message ... Is it possible to refer to the cell address generated within a cell to specify a location to paste. The macro below pastes all the data to cell (S27). I need the data to be pasted to the Value in cell (S27) not the cell itself. The formula already in this cell generates a cell address, currently ($s$28) and this will be changed by the user each time the macro is ran. Do I need to enter the word value somewhere??? Sub Macro3() ' ' Macro3 Macro ' Macro recorded 24.02.2005 ' ' Range("C20:O22").Select Selection.COPY Range("S27").Select Selection.PasteSpecial PASTE:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("N11").Select Application.CutCopyMode = False End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
refer to a cells value in a macro
loulou,
Replace: Range("S27").Select Selection.PasteSpecial PASTE:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False with Range(Range("S27").Value).PasteSpecial PASTE:=xlValues HTH, Bernie MS Excel MVP "loulou" wrote in message ... Is it possible to refer to the cell address generated within a cell to specify a location to paste. The macro below pastes all the data to cell (S27). I need the data to be pasted to the Value in cell (S27) not the cell itself. The formula already in this cell generates a cell address, currently ($s$28) and this will be changed by the user each time the macro is ran. Do I need to enter the word value somewhere??? Sub Macro3() ' ' Macro3 Macro ' Macro recorded 24.02.2005 ' ' Range("C20:O22").Select Selection.COPY Range("S27").Select Selection.PasteSpecial PASTE:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("N11").Select Application.CutCopyMode = False End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
refer to a cells value in a macro
Sorry Bob I wasn't very clear.
I realise that cannot have a formula and a value in the same cell. What I need the macro to do is to use the cell address generated within the formula of cell (S27) which is ($S$28), and to paste the data there. Thanks for your reply. "Bob Phillips" wrote: You cannot have a formula and a value in the same cell. Once you paste a value in, the formula is lost. -- HTH RP (remove nothere from the email address if mailing direct) "loulou" wrote in message ... Is it possible to refer to the cell address generated within a cell to specify a location to paste. The macro below pastes all the data to cell (S27). I need the data to be pasted to the Value in cell (S27) not the cell itself. The formula already in this cell generates a cell address, currently ($s$28) and this will be changed by the user each time the macro is ran. Do I need to enter the word value somewhere??? Sub Macro3() ' ' Macro3 Macro ' Macro recorded 24.02.2005 ' ' Range("C20:O22").Select Selection.COPY Range("S27").Select Selection.PasteSpecial PASTE:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("N11").Select Application.CutCopyMode = False End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
refer to a cells value in a macro
Thank you very much Bernie it works like a dream!!!
"Bernie Deitrick" wrote: loulou, Replace: Range("S27").Select Selection.PasteSpecial PASTE:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False with Range(Range("S27").Value).PasteSpecial PASTE:=xlValues HTH, Bernie MS Excel MVP "loulou" wrote in message ... Is it possible to refer to the cell address generated within a cell to specify a location to paste. The macro below pastes all the data to cell (S27). I need the data to be pasted to the Value in cell (S27) not the cell itself. The formula already in this cell generates a cell address, currently ($s$28) and this will be changed by the user each time the macro is ran. Do I need to enter the word value somewhere??? Sub Macro3() ' ' Macro3 Macro ' Macro recorded 24.02.2005 ' ' Range("C20:O22").Select Selection.COPY Range("S27").Select Selection.PasteSpecial PASTE:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("N11").Select Application.CutCopyMode = False End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
refer to two cells for location | Excel Discussion (Misc queries) | |||
how to find cells that refer to data in other cells in excel | Excel Discussion (Misc queries) | |||
Using cells to refer to worksheets | Excel Worksheet Functions | |||
To get a lot of charts that refer to different cells | Charts and Charting in Excel | |||
Refer to three cells to populate For next | Excel Programming |