excel - 大型数据集的两个值的 if 语句

Question

我与 VBA 斗争，并花了几天时间试图找到解决这个问题的方法。本质上，我有两个包含大量数据的电子表格。 “设计模块”工作表的 K 列包含与“输出”工作表的 C 列相同类型的值。我一直在尝试让我的脚本执行以下操作:
1. 对于“Design Mods”的 k 列中的每个单元格，检查“输出”电子表格的 c 列中是否有匹配的单元格
2. 如果找到匹配项，则使用“输出”的 b 列中的信息填充“设计模块”中的单元格到列
由于我缺乏经验，我只能设置下面的脚本，该脚本只能正确检查和拉取一个单元格。
我需要它来检查每个单元格与一系列其他单元格。
任何帮助/指导将不胜感激。
非常感谢!

Sub MatchValue_Test()
'Routine is meant to populate columns "Design Mods" Spreadsheet with affected calculations from the "Output" Spreadsheet

'Variables below refer to Design Mods spreadsheet
 Dim designmod As Worksheet '<-- Design Mods worksheet that we are comparing to the Output Data
 Dim DesignMod_DClrow As Integer '<-- Variable used to count to the last low in the DC Number Column of Design Mods Spreadsheet
 Dim designmoddc As Range '<-- Variable used to identify the range of values being checked in Design Mods is the DC Numbers Column K from K4 to the end of the column
 Dim valuetofind As String '<-- DC Number used as matching criteria between Design Mods spreadsheet and Output Data

 'Test Variables for integrating references to from Output worksheet
 Dim testset As Worksheet
 Dim test2_lrow As Integer
 Dim test As Range
 Dim valuetofindw2 As String



 'Variables below pertain the routine itself
 Dim found As Boolean '<-- this condition has to be false to start the routine

'Start of Routine
 found = False

'Definition of Data Ranges in Design Mods spreadsheet
 Set designmod = ActiveWorkbook.Worksheets("Sheet1")
 DesignMod_DClrow = designmod.Range("K4").End(xlDown).Row
 Set designmoddc = designmod.Range("K4:K" & DesignMod_DClrow)

 'Test variables for integrating values from Output worksheet
 Set testset = ActiveWorkbook.Worksheets("Sheet2")
 test2_lrow = testset.Range("C2").End(xlDown).Row
 Set test = testset.Range("C2:C" & test2_lrow)


 'Identify the value being matched against
  valuetofind = designmod.Range("L4").Value '<-- the script wont run if I have this value set to a range, and I need to figure out get this to loop so I  don't need a variable for every cell im checking against

 'test variables to figure out if statement
valuetofindw2 = testset.Range("C2").Value
valuetofindw3 = testset.Range("B2").Value
valuetofindw4 = designmod.Range("K4")

'If Statements performing the comparison



For Each Cell In designmoddc
     If Cell.Value = valuetofindw3 Then
     found = True
     End If
     Next
    If found = True Then
    designmoddc.Cells.Offset(0, 2).Value = testset.Range("B2")
    End If
 End Sub

Answer 1

你没有回答我的澄清问题...

我准备了一个解决方案，能够非常快速地工作(使用数组)。请备份您的工作簿，因为代码将重写 M:M 列中的匹配案例。

Sub MatchValue_TestArrays()
 Dim designMod As Worksheet, lastRowD As Long, testSet As Worksheet, lastRowT As Long
 Dim arrDes As Variant, arrTest As Variant, d As Long, t As Long, boolFound As Boolean

   Set designMod =  Worksheets("Sheet1")
   Set testSet =  Worksheets("Sheet2")
    lastRowD = designMod.Range("K" & Cells.Rows.Count).End(xlUp).Row
    lastRowT = testSet.Range("C" & Cells.Rows.Count).End(xlUp).Row
    arrDes = designMod.Range("K4:M" & lastRowD).value 'load the range in array
    arrTest = testSet.Range("B2:C" & lastRowT).value

    For d = 1 To UBound(arrDes, 1)
        For t = 1 To UBound(arrTest, 1)
            If arrDes(d, 1) = arrTest(t, 2) Then
                arrDes(d, 3) = arrTest(t, 1)'fill the array third column (M:M) with values of B:B testSheet...
                Exit For
            End If
        Next t
    Next d
    designMod.Range("K4:M" & lastRowD).value = arrDes' Drop the modified array
End Sub

请尝试更新的代码。它现在搜索所有出现的事件并将每个事件放在一个连续的列中:

Sub MatchValue_TestArrays_Extended()
 Dim designMod As Worksheet, lastRowD As Long, testSet As Worksheet, lastRowT As Long
 Dim arrDes As Variant, arrTest As Variant, d As Long, t As Long, col As Long

   Set designMod = Worksheets("Design") ' Worksheets("Sheet1")
   Set testSet = Worksheets("TestS") ' Worksheets("Sheet2")
    lastRowD = designMod.Range("K" & Cells.Rows.Count).End(xlUp).Row
    lastRowT = testSet.Range("C" & Cells.Rows.Count).End(xlUp).Row
    arrDes = designMod.Range("K4:AQ" & lastRowD).value
    arrTest = testSet.Range("B2:C" & lastRowT).value

    For d = 1 To UBound(arrDes, 1)
        col = 3 'the column where the occurrence will be put
        For t = 1 To UBound(arrTest, 1)
            If arrDes(d, 1) = arrTest(t, 2) Then
                arrDes(d, col) = arrTest(t, 1): col = col + 1
            End If
        Next t
    Next d
    designMod.Range("K4:AQ" & lastRowD).value = arrDes
End Sub