Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Function Value
Simplified Example without a macro: C11: =SUM($A2,B$3) I can drag that formula so it fills the range C11:F15 and it maintains the relative referencing. I need to be able to select a range of cells (C11:F15) and run a macro that enters the values returned by the function (=SUM($A2,B$3)). I'm extremely new to Excel macros, but have many years of programming and scripting experience. Thanks, JC -- carlyman ------------------------------------------------------------------------ carlyman's Profile: http://www.excelforum.com/member.php...o&userid=22440 View this thread: http://www.excelforum.com/showthread...hreadid=379833 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Function Value
If you are already copying down by hand in the sheet then you could simply copy the selected cells once you have copied down and move over one column and paste special as values. Or do you want a macro to both copy the formula in one column and then paste the values into the rext column? -- bhofsetz ------------------------------------------------------------------------ bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807 View this thread: http://www.excelforum.com/showthread...hreadid=379833 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Function Value
I currently have to copy the formula down all the rows and then over all the columns to get the values. I then copy all the cells and paste-special-values. The reason I don't keep the formula in all the cells addresses the following two issues: 1) People will change the value when doing custom scenarios (I know about the scenario function of Excel, but must users will not) and could potentially need to return that cell to the original value. 2) I am using a long SUMPRODUCT function to lookup the data. Having 50k-100k SUMPRODUCT calls slows the computer down considerably. So, being able to highlight the cells and running a macro to insert the value the formula (not the formula itself) would be ideal. -- carlyman ------------------------------------------------------------------------ carlyman's Profile: http://www.excelforum.com/member.php...o&userid=22440 View this thread: http://www.excelforum.com/showthread...hreadid=379833 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste Function Value
This code will fill the selected region with the formula "=sum($A2,B$3) while maintaining correct relative references. Then it will copy an paste special the resulting values. This assumes that you have selected the range from the top left t bottom right leaving your ActiveCell (the one that is not shaded) a the top left. Code ------------------- Sub FillRange() Dim fR As Integer, selRows As Integer, lR As Integer Dim fC As Integer, selCol As Integer, lC As Integer fR = ActiveCell.Row 'finds the first row of the selected area selRows = Selection.Rows.Count 'finds the number of rows in the selected area lR = fR + selRows - 1 'finds the last row of the selected area fC = ActiveCell.Column 'finds the first column of the selected area selCol = Selection.Columns.Count 'finds the number of columns in the selected area lC = fC + selCol - 1 'finds the last column of the selected area ActiveCell = "=sum($A2,B$3)" ActiveCell.AutoFill Destination:=Range(Cells(fR, fC), Cells(fR, lC)), Type:=xlFillDefault Range(Cells(fR, fC), Cells(fR, lC)).AutoFill Destination:=Selection, Type:=xlFillDefault Selection.Copy Selection.pastespecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False End Su ------------------- HT -- bhofset ----------------------------------------------------------------------- bhofsetz's Profile: http://www.excelforum.com/member.php...fo&userid=1880 View this thread: http://www.excelforum.com/showthread.php?threadid=37983 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with Paste function | Excel Discussion (Misc queries) | |||
Paste Special Function | Excel Worksheet Functions | |||
copy and paste function | Excel Worksheet Functions | |||
Paste all function | Excel Discussion (Misc queries) | |||
Paste Link Function | Excel Discussion (Misc queries) |