Paste Function Value

  • Thread starter Thread starter carlyman
  • Start date Start date
C

carlyman

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
 
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?
 
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.
 
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
 
Back
Top