vba 从两张纸上的不同位置减去值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9624811/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Subtract values from different locations on two sheets
提问by Stephan Daudt
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.
对此的理想解决方案是在 VBA 中，而不是在公式中，因为我每次都需要第一张纸自行重置，因此公式会被覆盖。
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 Sheet2by subtracting the "Sold" on Sheet1from the "Count" on Sheet2. The result would look something like this:
我想要做的是让 VBA 代码Sheet2通过Sheet1从“计数”中减去“已售出”来更新Sheet2。结果看起来像这样：
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 Sheet1will have users paste info from a website so it will have to base the search on the Item number and not column A.
我已经能够找出几个代码，但我永远无法让它们适合。并且Sheet1会让用户粘贴来自网站的信息，因此它必须基于项目编号而不是 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?
我认为最好的办法是重新开始，以确保我不会因为从错误的方向开始而搞砸了。有谁知道从哪里开始？
采纳答案by Siddharth Rout
Another way using Vlookup
使用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
快照
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?
您目前正在进行的清洁不正确。如果我手动清洁床单应该是这样的吗？
Also if you notice that then numbers are stored as text or have blank spaces. Vlookup will fail in these scenarios.
此外，如果您注意到数字存储为文本或有空格。Vlookup 在这些情况下会失败。
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
1）您需要在可以更有效地清理数据以供输入的地方使用宏
2)Either you move the "SKU" before the "QTY" and then use Vlookup ORuse an alternate method.
2）要么你的“QTY”前移动“SKU”，然后使用VLOOKUP OR使用替代方法。
3)If you are interested in an alternate method then see (Section 4) in this link
3）如果您对替代方法感兴趣，请参阅此链接中的（第 4 节）
http://www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/
http://www.siddharthrout.com/2011/07/14/find-and-findnext-in-excel-vba/
回答by ranganatha channarayapatna
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:
Sheet2 中的 2 到 8 行和 Sheet1 中的 2 到 4 行是基于引用的示例。但是，可以使用 Cells.End 方法捕获每个工作表中的行数，即销售数据表和库存数据表，如下所示：
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
上面的循环遍历所有有数据的单元格，并通过 If 语句扣除股票
回答by Jerry Beaucaire
Formula method:
公式法：
On sheet2 in C2 put this formula
=IF(ISNUMBER(MATCH(A2, Sheet1!A:A, 0)), B2 - VLOOKUP(A2, Sheet1!A:B, 2, 0), B2)
- Copy that formula down the data set
- Copy the set of new data in C2:C???
- Click on B2 and select Edit > Paste Special > Values
- Clear column C...you're done
在 C2 中的 sheet2 上放上这个公式
=IF(ISNUMBER(MATCH(A2, Sheet1!A:A, 0)), B2 - VLOOKUP(A2, Sheet1!A:B, 2, 0), B2)
- 将该公式复制到数据集
- 复制 C2:C 中的新数据集？？？
- 单击 B2 并选择“编辑”>“选择性粘贴”>“值”
- 清除 C 列...你完成了
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
