在工作表B中的工作表A中查找值，然后在相应的工作表B单元格中运行 [英] Look for values from sheet A in sheet B, and then do function in corresponding sheet B cell

问题描述

我需要从工作表B中的工作表A中查找值，然后在VBA中的相应工作表B单元中运行，以便可以更改工作表1中的数据，按一个按钮并使它工作.字母标题表示excel中行的名称.xs只是说这是我不想处理的数据.我有工作表1和工作表2.

I need to ook for values from sheet A in sheet B, and then do function in corresponding sheet B cell in VBA so I can change the data in worksheet 1, press a button, and have it work. The Letter headings denote the name of the rows in excel. The xs are just saying that it is data I do not want to manipulate. I have sheet 1 and sheet 2.

 Worksheet 1                        
H     I            J          K          L           M          N
1   xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    100     xxxxxxxx
2   xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    200     xxxxxxxx
3   xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    300     xxxxxxxx
4   xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    400     xxxxxxxx
5   xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    500     xxxxxxxx
6   xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    600     xxxxxxxx
7   xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    700     xxxxxxxx
8   xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    800     xxxxxxxx
9   xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    900     xxxxxxxx
10  xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    1000    xxxxxxxx
11  xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    1100    xxxxxxxx
12  xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    1200    xxxxxxxx
13  xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    1300    xxxxxxxx
14  xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    1400    xxxxxxxx
15  xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    1500    xxxxxxxx
16  xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    1600    xxxxxxxx
17  xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    1700    xxxxxxxx
18  xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    1800    xxxxxxxx
19  xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    1900    xxxxxxxx
20  xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    2000    xxxxxxxx

Worksheet 2                     
   H        I         J           K           L         M     N
   2    xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    25  xxxxxxxx
   5    xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    40  xxxxxxxx
   9    xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    35  xxxxxxxx
  11    xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    50  xxxxxxxx
  10    xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    65  xxxxxxxx
   6    xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    90  xxxxxxxx
   7    xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    10  xxxxxxxx

以便在工作表1中减去相应的值，从而更改M列的值.

So that the corresponding values are subtracted in worksheet 1, thus changing the values of the M column.

Worksheet 1 - new                   
H     I            J          K          L           M          N
1   xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    100     xxxxxxxx
2   xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    175     xxxxxxxx
3   xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    300     xxxxxxxx
4   xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    400     xxxxxxxx
5   xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    460     xxxxxxxx
6   xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    510     xxxxxxxx
7   xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    690     xxxxxxxx
8   xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    800     xxxxxxxx
9   xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    865     xxxxxxxx
10  xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    935     xxxxxxxx
11  xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    1050    xxxxxxxx
12  xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    1200    xxxxxxxx
13  xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    1300    xxxxxxxx
14  xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    1400    xxxxxxxx
15  xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    1500    xxxxxxxx
16  xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    1600    xxxxxxxx
17  xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    1700    xxxxxxxx
18  xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    1800    xxxxxxxx
19  xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    1900    xxxxxxxx
20  xxxxxxxx    xxxxxxxx    xxxxxxxx    xxxxxxxx    2000    xxxxxxxx

推荐答案

这将执行对 Sheet B

Sub New2VBA()
    Dim A As Worksheet, B As Worksheet
    Dim i As Long, j As Long, v As Variant
    Dim N As Long, M As Long
    Set A = Sheets("Sheet A")
    Set B = Sheets("Sheet B")
    N = A.Cells(Rows.Count, "A").End(xlUp).Row
    M = B.Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To N
        v1 = A.Cells(i, "A").Value
        v2 = A.Cells(i, "B").Value
        For j = 1 To M
            If v1 = B.Cells(j, "A").Value Then
                B.Cells(j, "B").Value = B.Cells(j, "B").Value - v2
                Exit For
            End If
        Next j
    Next i
End Sub

EDIT#1:

这是更新的代码.....删除旧代码:

Here is the updated code.....remove the old code:

这将基于工作表 B (2上的增量值)更新工作表 A (您的工作表1)上的值)

This will update the values on sheet A (your worksheet 1) based on the delta values on sheet B (2)

Sub New2VBA()
    Dim A As Worksheet, B As Worksheet
    Dim i As Long, j As Long, v As Variant
    Dim N As Long, M As Long
    Set A = Sheets("Sheet A")
    Set B = Sheets("Sheet B")
    '
    '   A is worksheet 1
    '   B is worksheet 2
    '
    N = A.Cells(Rows.Count, "H").End(xlUp).Row
    M = B.Cells(Rows.Count, "H").End(xlUp).Row

    For i = 1 To M
        v1 = B.Cells(i, "H").Value
        v2 = B.Cells(i, "M").Value
        For j = 1 To N
            If v1 = A.Cells(j, "H").Value Then
                A.Cells(j, "M").Value = A.Cells(j, "M").Value - v2
                Exit For
            End If
        Next j
    Next i
End Sub

这篇关于在工作表B中的工作表A中查找值，然后在相应的工作表B单元格中运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！