根据另一个电子表格的值将值添加到电子表格中

Question

我有两个工作簿；"主"one_answers" Zcontrol"。他们分别有一个称为"主"one_answers" Zcontrol"的工作表。

如果在" zcontrol"的两个列" a"中找到了" master"one_answers" master"的" m"列中的特定短语，则需要将字符串添加到" master"的" n"列。

" master"的m"可能包含的信息可能比" zcontrol"列" a" a" a"中的短语更多的信息，因此它不会完全匹配，但将包含列的值" a"。在" zcontrol"中以序列顺序分开单词。

需要将要添加到" master"列的字符串从" zcontrol"列中的单元格中拉出，该字符与与列中的匹配值相同的" zcontrol"列中的" b"中的字符串。" zcontrol"。

我用普通语言需要的示例：

如果" Zcontrol"的" A2"与" Master"的" M44"的一部分相匹配，则将" Zcontrol"的" B2"添加到" Master"的" N44"。

找到下一场比赛并执行相同的逻辑...

我需要用简单语言做的宏来完成的另一个示例：

如果" Zcontrol"的" A49"与" Master"的" M2"的一部分相匹配，则将" Zcontrol"的" B49"添加到" Master"的" N2"。

找到下一场比赛并执行相同的逻辑...

这是我到目前为止所拥有的：

Sub Adder()
Dim WS_Master As Worksheet 
Dim WS_Control As Worksheet 
Dim WS_Master_Lastrow As Long
Dim WS_Control_Lastrow As Long
Dim i As Long
Dim j As Long
Dim k As Long
Set WS_Master = Workbooks("Master").Worksheets("Master")
Set WS_Control = Workbooks("zControl").Worksheets("zControl")
'Find last row of WS_Master
WS_Master_Lastrow = WS_Master.Cells.Find("*", [A1], , , xlByRows, 
xlPrevious).Row
'find last column of WS_Master
WS_Master_Lastcol = WS_Master.Cells.Find("*", [A1], , , xlByColumns, 
xlPrevious).Column
'Find last row of WS_Control
WS_Control_Lastrow = WS_Control.Cells.Find("*", [A1], , , xlByRows, 
xlPrevious).Row
'Add control sheet data to a array
Dim ControlData() As String
ReDim ControlData(1 To WS_Control_Lastrow, 1 To 3)
For i = 1 To WS_Control_Lastrow
ControlData(i, 1) = Trim(WS_Control.Range("A" & i).Value)
ControlData(i, 2) = Trim(WS_Master.Range("M" & i).Value)
ControlData(i, 3) = Trim(WS_Control.Range("B" & i).Value)
Next i
'Loop through Master sheet
For i = 1 To WS_Master_Lastrow
'Loop through Control sheet
For j = 2 To WS_Control_Lastrow
    'if both string found
    If InStr(1, WS_Control.Range("A" & i).Value, ControlData(j, 1), 
vbTextCompare) > 0 And _
    InStr(1, WS_Master.Range("M" & i).Value, ControlData(j, 2), 
vbTextCompare) > 0 Then
        'Add Save Value to the row
        For k = 1 To WS_Master_Lastrow
            If WS_Master.Cells(i, j).Value <> "" Then
                WS_Master.Range("N" & j).Value = WS_Control.Cells(i, 2)
            End If
        Next k
        Exit For
    End If
Next j
Next i
MsgBox "Completed!", vbInformation, ""
End Sub

在这一点上，我挂在if语句中。我几乎可以确定该错误的原因是" .range"参数，但无法弄清楚如何修复它，因此宏将必要的值添加到" N"列中的正确单元格中。

这很容易成为我在VBA中做过的最复杂的事情，也是我第一次使用数组。我提到这一点的原因是，即使没有错误，我也可能不会做我想做的事情。

任何想法或方向都将不胜感激。

感谢您抽出宝贵的时间阅读本文，并提供您的输入。

编辑：

这是我现在所在的地方。

Sub Adder()
Dim WS_Master As Worksheet 'This was GNC
Dim WS_Control As Worksheet 'This was the ControlSheet.
Dim WS_Master_Lastrow As Long
Dim WS_Control_Lastrow As Long
Dim i As Long
Dim j As Long
Dim k As Long
Set WS_Master = Workbooks("Master").Worksheets("Master")
Set WS_Control = Workbooks("zControl").Worksheets("zControl")
'Find last row of WS_Master
WS_Master_Lastrow = WS_Master.Cells.Find("*", [A1], , , xlByRows, 
xlPrevious).Row
'find last column of WS_Master
WS_Master_Lastcol = WS_Master.Cells.Find("*", [A1], , , xlByColumns, 
xlPrevious).Column
'Find last row of WS_Control
WS_Control_Lastrow = WS_Control.Cells.Find("*", [A1], , , xlByRows, 
xlPrevious).Row
'Add control sheet data to a array
Dim ControlData() As String
ReDim ControlData(1 To WS_Control_Lastrow, 1 To 3)
For i = 1 To WS_Control_Lastrow
ControlData(i, 1) = Trim(WS_Control.Range("A" & i).Value)
ControlData(i, 2) = Trim(WS_Master.Range("M" & i).Value)
ControlData(i, 3) = Trim(WS_Control.Range("B" & i).Value)
Next i
'Loop through data sheet
For i = 1 To WS_Master_Lastrow
'Loop through control sheet
For j = 2 To WS_Control_Lastrow
    'if both string found
    If InStr(1, WS_Control.Range("A" & i).Value, ControlData(j, 1), 
vbTextCompare) > 0 And _
    InStr(1, WS_Master.Range("M" & i).Value, ControlData(j, 2), 
vbTextCompare) > 0 Then
        'Add Save Value to the row
        WS_Master.Range("N" & j).Value = WS_Control.Cells(i, 2)

    End If
Next j
Next i
MsgBox "Completed!", vbInformation, ""
End Sub

所做的就是将" b2"的值从" zcontrol"添加到" zcontrol"到" master"的" n2"，" zcontrol"的" b3"到" master"的" n3"，等等... <... <...

任何其他想法将不胜感激。如前所述，我是VBA的新手，并且正在学习时正在学习。

Answer 1

sub adder()
dim zcontrol as worksheet
dim wsmaster as worksheet
application.screenupdating=false
application.displayalerts=false
On error goto Reset_settings
Set wsmaster = Workbooks("Master").Worksheets("Master")
Set zcontrol = Workbooks("zControl").Worksheets("zControl")
zcontrol_lr=zcontrol.range("A" & rows.count).end(xlup).row
wsmaster_lr=wsmaster.range("A" & rows.count).end(xlup).row
wsmaster_lc=wsmaster.cells(1,columns.count).end(xltoleft).column
For i=2 to wsmaster_lr
    for j=2 to zcontrol_lr
        if instr(1,Trim(wsmaster.range("M" & i).value), trim(zcontrol.range("A"& j).value),vbBinaryCompare) > 0 then
            wsmaster.range("N"& i).value=zcontrol.cells(j,2).value
        end if
    next j
next i
Msgbox "Completed!",vbinfomration,""

reset_settings:
application.screenupdating=true
application.displayalerts=true

end sub

我认为这对您有帮助...我还没有测试过代码。尝试一下。