If statement for two values for large set of data

Question

I struggle with VBA and have spent a few days trying to find a solution to this problem. Essentially, I have two spreadsheets with large sets of data. Column K of "Design Mods" worksheet contains the same types of values as Column C of the "Output" Worksheet. I've been trying to get my script to do the following: 1. for each cell in column k of "Design Mods", check if there is a matching cell in column c of the "output" spreadsheet 2. if a match is found, then populate the cell in "Design Mods" to columns over with the information from column b of "Output" Because of my lack of experience, I've only been able to setup the script below which only checks and pulls correctly for one cell. I need it to check each cell against a range of other cells. Any help/guidance would be very much appreciated. Thank you very much!

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

user_2233308 · Answer

You did not answer my clarification questions...

I prepared a solution, able to work very fast (using arrays). Please back-up your workbook, because the code will rewrite the matching cases in column 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

Try the updated code, please. It searches now for all occurrences and put each one in a consecutive column:

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

user_478884 · Answer

Using Match() is fast when your data is on a worksheet:

Sub MatchValue_Test()

    Dim wsDesign As Worksheet, wsOut As Worksheet, m, c As Range

    Set wsDesign = ActiveWorkbook.Worksheets("Sheet1")
    Set wsOut = ActiveWorkbook.Worksheets("Sheet2")

    For Each c In wsDesign.Range(wsDesign.Range("K4"), _
                                 wsDesign.Cells(Rows.Count, "k").End(xlUp)).Cells

        m = Application.Match(c.Value, wsOut.Columns("C"), 0)
        If Not IsError(m) Then
            'if Match() found a hit then m will be the row number on sheet2
            c.Offset(0, 2).Value = wsOut.Cells(m, "B").Value
        End If

    Next c

End Sub

If statement for two values for large set of data

2 Answers