Macro that replaces pre-existing number

D

dnasim

New Member
Joined
Jul 11, 2014
Messages
16
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


[TABLE="class: cms_table"]
<tbody>[TR]
[TD="bgcolor: #D0D0D0, align: center"]Date[/TD]
[TD="bgcolor: #D0D0D0, align: center"]Number[/TD]
[TD="bgcolor: #D0D0D0, align: center"]Item[/TD]
[TD="bgcolor: #D0D0D0, align: center"]Quantity[/TD]
[TD="bgcolor: #D0D0D0, align: center"]Created From[/TD]
[TD="bgcolor: #D0D0D0, align: center"]Class[/TD]
[TD="bgcolor: #D0D0D0, align: center"]Company Name[/TD]
[TD="bgcolor: #D0D0D0, align: center"]Location[/TD]
[TD="bgcolor: #D0D0D0, align: center"]Shipping Country[/TD]
[TD="bgcolor: #D0D0D0, align: center"]Sum of Amount[/TD]
[/TR]
[TR]
[TD="align: right"]8/8/2014[/TD]
[TD]140981[/TD]
[TD]AHFMT-001[/TD]
[TD="align: right"]2[/TD]
[TD]rma[/TD]
[TD]Webstore/Phone Sales[/TD]
[TD]store[/TD]
[TD]africa[/TD]
[TD]United States[/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TD="align: right"]7/7/2014[/TD]
[TD]140981[/TD]
[TD]CHDX-302[/TD]
[TD="align: right"]2[/TD]
[TD]rma[/TD]
[TD]Webstore/Phone Sales[/TD]
[TD]store[/TD]
[TD]japan[/TD]
[TD]United States[/TD]
[TD="align: right"]240[/TD]
[/TR]
[TR]
[TD="align: right"]6/25/2014[/TD]
[TD]140981[/TD]
[TD]ASDMC-332[/TD]
[TD="align: right"]1[/TD]
[TD]rma[/TD]
[TD]Webstore/Phone Sales[/TD]
[TD]sore[/TD]
[TD]texas[/TD]
[TD]United States[/TD]
[TD="align: right"]2.76[/TD]
[/TR]
[TR]
[TD="align: right"]6/20/2014[/TD]
[TD]141984[/TD]
[TD]CHHX-302[/TD]
[TD="align: right"]30[/TD]
[TD]rma[/TD]
[TD]Retailer[/TD]
[TD]Ama[/TD]
[TD]africa[/TD]
[TD]United States[/TD]
[TD="align: right"]3,7.34[/TD]
[/TR]
[TR]
[TD="align: right"]6/20/2014[/TD]
[TD]142008[/TD]
[TD]280-00133-000[/TD]
[TD="align: right"]1[/TD]
[TD]rma[/TD]
[TD]Webstore/Phone Sales[/TD]
[TD]- None -[/TD]
[TD]japan[/TD]
[TD]France[/TD]
[TD="align: right"]5.30[/TD]
[/TR]
[TR]
[TD="align: right"]6/20/2014[/TD]
[TD]142009[/TD]
[TD]210-00510-000[/TD]
[TD="align: right"]1[/TD]
[TD]rma[/TD]
[TD]Webstore/Phone Sales[/TD]
[TD]- None -[/TD]
[TD]texas[/TD]
[TD]France[/TD]
[TD="align: right"]124.08[/TD]
[/TR]
[TR]
[TD="align: right"]6/20/2014[/TD]
[TD]142010[/TD]
[TD]210-00128-R1[/TD]
[TD="align: right"]1[/TD]
[TD]rma[/TD]
[TD]Webstore/Phone Sales[/TD]
[TD]- None -[/TD]
[TD]africa[/TD]
[TD]France[/TD]
[TD="align: right"]68.73[/TD]
[/TR]
</tbody>[/TABLE]





Here are the Demand Planning Prem and Reg columns

[TABLE="class: cms_table"]
<tbody>[TR]
[TD]Date[/TD]
[TD]SKU[/TD]
[TD]Name[/TD]
[TD]Begin FG's[/TD]
[TD]Add Returns[/TD]
[TD]Demand FG[/TD]
[TD]End FG's[/TD]
[TD]Weeks FG[/TD]
[TD]Excess FG[/TD]
[/TR]
[TR]
[TD="align: right"]6/20/2014[/TD]
[TD]ASDRK-301[/TD]
[TD]Tent[/TD]
[TD="align: right"]5718[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]5661[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]5339[/TD]
[/TR]
[TR]
[TD="align: right"]6/27/2014[/TD]
[TD]ASDRK-301[/TD]
[TD]Tent[/TD]
[TD="align: right"]5661[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]5632[/TD]
[TD="align: right"]156[/TD]
[TD="align: right"]5310[/TD]
[/TR]
[TR]
[TD="align: right"]7/4/2014[/TD]
[TD]ASDRK-301[/TD]
[TD]Tent[/TD]
[TD="align: right"]5632[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]5598[/TD]
[TD="align: right"]147[/TD]
[TD="align: right"]5276[/TD]
[/TR]
[TR]
[TD="align: right"]7/11/2014[/TD]
[TD]ASDRK-301[/TD]
[TD]Tent[/TD]
[TD="align: right"]5598[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]5560[/TD]
[TD="align: right"]146[/TD]
[TD="align: right"]5238[/TD]
[/TR]
[TR]
[TD="align: right"]7/18/2014[/TD]
[TD]ASDRK-301[/TD]
[TD]Tent[/TD]
[TD="align: right"]5560[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]5522[/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]5200[/TD]
[/TR]
[TR]
[TD="align: right"]7/25/2014[/TD]
[TD]ASDRK-301[/TD]
[TD]Tent[/TD]
[TD="align: right"]5522[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]5484[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]5162[/TD]
[/TR]
[TR]
[TD="align: right"]8/1/2014[/TD]
[TD]ASDRK-301[/TD]
[TD]Tent[/TD]
[TD="align: right"]5484[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]5446[/TD]
[TD="align: right"]143[/TD]
[TD="align: right"]5124[/TD]
[/TR]
[TR]
[TD="align: right"]8/8/2014[/TD]
[TD]ASDRK-301[/TD]
[TD]Tent[/TD]
[TD="align: right"]5446[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]5408[/TD]
[TD="align: right"]142[/TD]
[TD="align: right"]5086[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
This code will convert formulas in column E on the active sheet to values.
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Macro for input box to replace formula cells with 0
Replies
2
Views
771
dnasim
D

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top