CODEWITHSUNDEEP
vbaexcel
Jon
Jon

Reputation: 1

Macro to replace pre-existing number

I have this VBA code for a Macro I am trying to run. What it does is that it takes data from ItemReceipts and if it matches the criteria it places the summed number from column D into column E of the Demand Planning Prem sheet.

Although the macro works the issue I am having is that in the column where I need to data to be placed, there is a formula for running averages in each cell. When I run my macro it seems to add on to that already existing number, but I want it to replace that number and formula completely.

Can anyone lend me a hand?

Sub subStuff()
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) 'Edit sheet name
Set sh2 = wb2.Sheets(1) 'Edit sheet name
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

ItemReceipts

+------------+---------+-------+---------+
| Date ----- | Number-|Item | Quantity |
+------------+---------+-------+---------+
| 8/8/2014 | 140981 |AHF-001| 5 |
+------------+---------+-------+---------+
| 8/3/2014 |140981 |AHF-001| 3 |
+------------+---------+-------+---------+

Demand Planning Prem

+------------+---------+-------+---------+------------+
| Date----- | SKU --- |Name | FG's--- | Add Returns|
+------------+---------+-------+---------+-------------
| 8/8/2014 | AHF-001 |Tent | 5744 | 4 |
+------------+---------+-------+---------+------------+

So ItemReceipt would get scanned and if the Item matcehs the SKU and if the Dates of ItemReceipts fall within the week of Date for Demand Planning Prem then I want that total Quantity to replace the number in Add Returns.

So in this case take the 5 + 3 = 8 and put 8 in place of the 4. As of right now what it does is 4 + 8 = 12 which is not what I want.

Upvotes: 0

Views: 185

Answers (2)

CaptainABC
CaptainABC

Reputation: 1239

I agreed with @IAmDranged.

Edit your code to the below:

    Sub subStuff()
    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) 'Edit sheet name
    Set sh2 = wb2.Sheets(1) 'Edit sheet name
    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

sh2.Range("E" & fLoc.Row) = "0" ' have added this line.

                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

This way before you start your loop through all the values you can find, the "Add Returns" value of the found item will be "0". So when you start your script it will go something like this:

0 + 5 + 3 = 8 .... Essentially replacing 4 to 8 as requested :)

Let me know if this solves your problem.

Upvotes: 0

IAmDranged
IAmDranged

Reputation: 3020

As a quick fix just try initializing your sh2.Range("E" & fLoc.Row).value to 0 just before your do loop while

Upvotes: 0

Related Questions