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

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 and paste special the resulting values.

This assumes that you have selected the range from the top left to bottom right leaving your ActiveCell (the one that is not shaded) at the top left.

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 Sub

HTH