CODEWITHSUNDEEP
vbaexcelexcel-2003
Stephan Daudt
Stephan Daudt

Reputation: 119

Subtract values from different locations on two sheets

The ideal solution for this would be in VBA and not a formula because I need the first sheet to reset itself each time thus a formula would get overwritten.

I have two sheets:

Sheet1 (Subtract Inventory)

Item#   Sold
1022    23
1024    56
1025    52

Sheet2 (Count)

Item#   Count
1020    1027
1021    99
1022    76
1023    128
1024    57
1025    1023
1026    987

What I would like to do is have VBA code that will update Sheet2 by subtracting the "Sold" on Sheet1 from the "Count" on Sheet2. The result would look something like this:

Sheet2 (Count)

Item#   Count
1020    1027
1021    99
1022    53
1023    128
1024    1
1025    971
1026    987

I have been able to figure out several codes but I can never get them to fit. And Sheet1 will have users paste info from a website so it will have to base the search on the Item number and not column A.

I think the best thing to do is a restart to make sure that I am not messing up by starting off in the wrong direction. Does anyone have any idea where to begin?

Upvotes: 2

Views: 7375

Answers (3)

Siddharth Rout
Siddharth Rout

Reputation: 149305

Another way using Vlookup

Hope this is what you are trying?

TRIED AND TESTED

Option Explicit

Sub Sample()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim ws1LastRow As Long, ws2LastRow As Long, i As Long
    Dim SearchRange As Range

    Set ws1 = Sheets("Sheet1")
    ws1LastRow = ws1.Range("A" & ws1.Rows.Count).End(xlUp).Row

    Set SearchRange = ws1.Range("A1:B" & ws1LastRow)

    Set ws2 = Sheets("Sheet2")

    With ws2
        ws2LastRow = .Range("A" & .Rows.Count).End(xlUp).Row

        For i = 2 To ws2LastRow
            On Error Resume Next
            If Not IsError(Application.WorksheetFunction.VLookup(.Range("A" & i).Value, SearchRange, 2, False)) Then
                .Range("B" & i).Value = .Range("B" & i).Value - _
                Application.WorksheetFunction.VLookup(.Range("A" & i).Value, SearchRange, 2, False)
            End If
            On Error GoTo 0
        Next i
    End With

    '~~> Clear Sheet1 for next input
    ws1.Cells.ClearContents

    '~~> Clean Up
    Set SearchRange = Nothing
    Set ws1 = Nothing
    Set ws2 = Nothing
End Sub

SNAPSHOT

enter image description here

FOLLOWUP

The cleaning that you are doing at the moment is not correct. If I manually do the cleaning the sheet should look like this?

enter image description here

Also if you notice that then numbers are stored as text or have blank spaces. Vlookup will fail in these scenarios.

enter image description here

In such a case, I recommend the following

1) You need to have a macro in places which cleans your data more effectively for input

2) Either you move the "SKU" before the "QTY" and then use Vlookup OR use an alternate method.

3) If you are interested in an alternate method then see (Section 4) in this link

http://www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/

Upvotes: 1

ranganatha channarayapatna
ranganatha channarayapatna

Reputation: 13

The problem can be resolved through a simple code as under:

For sht2 = 2 To 8 ' Sheet2 rows
For sht1 = 2 To 4 ' Sheet1 rows
If Cells(sht2, 1).Value = Sheet1.Cells(sht1, 1).Value Then
Cells(sht2, 2).Value = Cells(sht2, 2).Value - Sheet1.Cells(sht1, 2).Value
End If
Next
Next

The 2 to 8 rows in Sheet2 and 2 to 4 rows in Sheet1 are based on the example quoted. However the number of rows in each sheet ie Sales Data Sheet and the Stock data sheet can be captured by use of Cells.End method as in:

For sht2 = 2 to sheet2.Cells(2,2).End(XlDown).Row +1 ' for Sheet2 rows
For sht1 = 2 to Sheet1.Cells(2,2).End(XlDown).Row +1 ' for Sheet1 rows

The loop above iterates through all the cells having data and deducts the stock through the If statement

Upvotes: 0

Jerry Beaucaire
Jerry Beaucaire

Reputation: 3197

Formula method:

  1. On sheet2 in C2 put this formula

    =IF(ISNUMBER(MATCH(A2, Sheet1!A:A, 0)), B2 - VLOOKUP(A2, Sheet1!A:B, 2, 0), B2)

  2. Copy that formula down the data set
  3. Copy the set of new data in C2:C???
  4. Click on B2 and select Edit > Paste Special > Values
  5. Clear column C...you're done

Macro method (same approach)

Sub UpdateSheet2()
Dim LR As Long

With Sheets("Sheet2")
    LR = .Range("A" & .Rows.Count).End(xlUp).Row
    With .Range("C2:C" & LR)
        .Formula = "=IF(ISNUMBER(MATCH(A2, Sheet1!A:A, 0)), B2 - VLOOKUP(A2, Sheet1!A:B, 2, 0), B2)"
        .Offset(, -1).Value = .Value
        .Value = ""
    End With
End With

End Sub

Upvotes: 0

Related Questions