Macro that replaces pre-existing number

[dnasim]

I am trying to have this Macro take data from workbook1 (ItemReceipts) column A and column C and matches it with the data from column A and B of Workbook2, named Demand Planning Prem respectively, take numbers from column D of ItemReceipts and sum it up, and then place that sum in column E of Demand Planning Prem and Reg.

Sub AddReturns()
Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, c As Range
Dim fLoc As Range, fAdr As String
Set wb1 = Workbooks("ItemReceipts")
Set wb2 = Workbooks("Demand Planning Prem")
Set sh1 = wb1.Sheets(1)
Set sh2 = wb2.Sheets(1)
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh1.Range("A2:A" & lr)
For Each c In rng
Set fLoc = sh2.Range("B2", sh2.Cells(Rows.Count, 2).End(xlUp)).Find(c.Offset(0, 2).Value, , xlValues, xlWhole)
If Not fLoc Is Nothing Then
fAdr = fLoc.Address


Do
If Application.WeekNum(fLoc.Offset(0, -1).Value) = Application.WeekNum(c.Value) Then
sh2.Range("E" & fLoc.Row) = sh1.Range("D" & c.Row) + sh2.Range("E" & fLoc.Row)
Exit Do
End If
Set fLoc = sh2.Range("B2", sh2.Cells(Rows.Count, 2).End(xlUp)).FindNext(fLoc)
Loop While fAdr <> fLoc.Address
End If
Next
End Sub

The issue I am having is that prior to the macro running I have AVERAGE formulas in each of the cells in column E of Workbook2, and when I run the macro it seems to add on to the Average, but I want it to remove the formula and simple display the total sum.

Here are the ItemReceipt Columns

Date	Number	Item	Quantity	Created From	Class	Company Name	Location	Shipping Country	Sum of Amount
8/8/2014	140981	AHFMT-001	2	rma	Webstore/Phone Sales	store	africa	United States	46
7/7/2014	140981	CHDX-302	2	rma	Webstore/Phone Sales	store	japan	United States	240
6/25/2014	140981	ASDMC-332	1	rma	Webstore/Phone Sales	sore	texas	United States	2.76
6/20/2014	141984	CHHX-302	30	rma	Retailer	Ama	africa	United States	3,7.34
6/20/2014	142008	280-00133-000	1	rma	Webstore/Phone Sales	- None -	japan	France	5.30
6/20/2014	142009	210-00510-000	1	rma	Webstore/Phone Sales	- None -	texas	France	124.08
6/20/2014	142010	210-00128-R1	1	rma	Webstore/Phone Sales	- None -	africa	France	68.73

<tbody>
</tbody>

Here are the Demand Planning Prem and Reg columns

Date	SKU	Name	Begin FG's	Add Returns	Demand FG	End FG's	Weeks FG	Excess FG
6/20/2014	ASDRK-301	Tent	5718	1	58	5661	98	5339
6/27/2014	ASDRK-301	Tent	5661	7	36	5632	156	5310
7/4/2014	ASDRK-301	Tent	5632	4	38	5598	147	5276
7/11/2014	ASDRK-301	Tent	5598	0	38	5560	146	5238
7/18/2014	ASDRK-301	Tent	5560	0	38	5522	145	5200
7/25/2014	ASDRK-301	Tent	5522	0	38	5484	144	5162
8/1/2014	ASDRK-301	Tent	5484	0	38	5446	143	5124
8/8/2014	ASDRK-301	Tent	5446	0	38	5408	142	5086

<tbody>
</tbody>

 

[pbornemeier]

This code will convert formulas in column E on the active sheet to values.

Sub ConvertFormulasToText()

    Dim lLastERow As Long
    
    With ActiveSheet
        lLastERow = .Cells(.Rows.Count, 5).End(xlUp).Row
        With .Range("E2:E" & lLastERow)
            .Copy
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=False
        End With
    End With

End Sub