CODEWITHSUNDEEP
excelvba
Alex Hagood
Alex Hagood

Reputation: 5

Search for the individual values from Sheet1 column B looking in sheet2 column B, and replace sheet1 value

I need help writing a VBA macro that will search for the individual values from Sheet1 column B looking in sheet2 column B, and replace the sheet1 value with the associated value in sheet 2 column C. In other words, take value in sheet1 B2 and search for same value in Sheet2 column B. When found, take value in Column C right next to it and replace sheet1 B2 with the value found in sheet2 Column C. Thanks in advance for the help!

I was trying to use the code below, but the item replacement line (marked with-->) is not working for some reason, I keep getting a

run-time error 13: type mismatch

but can't figure out wat I did wrong.

    Dim wb As Workbook, firstWs As Worksheet, secondWs As Worksheet
    Dim matchIndex As Integer

    Set wb = ThisWorkbook
    Set firstWs = wb.Worksheets(1)
    Set secondWs = wb.Worksheets(2)

    Application.ScreenUpdating = False

    ' We'll start at i=2 to account for the header
    For i = 2 To firstWs.Range("A2:A2000").Rows.Count
        On Error Resume Next
        ' MATCH will find the row number in sheet 2 - change the range specifications as needed
        matchIndex = Application.WorksheetFunction.Match(firstWs.Range("B" & i), secondWs.Range("B2:B3000"), 0)
        Err.Clear
        On Error GoTo 0

        ' MATCH will throw an error if it finds no results.
        ' Hence: if matchindex contains an error, do nothing.
        ' But if it doesn't contain an error, it must contain a row number - so we can proceed.
        If Not Application.WorksheetFunction.IsNA(matchIndex) Then
    -->     firstWs.Range("B" + i).Value = secondWs("C" + matchIndex).Value
        End If
    Next i

    Application.ScreenUpdating = True

Upvotes: 0

Views: 74

Answers (2)

Haluk
Haluk

Reputation: 1546

Another alternative is using SQL/ADO with VBA to update the table in Sheet1.

So; assuming the table in Sheet1 is something like this;

ID POSITION NAME
503 Default Bob
504 Default Trevor
501 Default Susan
502 Default Mary
506 Default David
505 Default Kyle

and, the table in Sheet2 is;

GENDER ID POSITION
Female 501 Manager
Female 502 Asst. Man.
Male 503 Engineer
Male 504 Lawyer
Male 505 Driver
Male 506 Staff

The below code will match the columns labelled as ID in both sheets and will update the data in Sheet1 column labelled as Position with the corresponding data in Sheet2

So, the final table in Sheet1 will be this, after the code is executed;

ID POSITION NAME
503 Engineer Bob
504 Lawyer Trevor
501 Manager Susan
502 Asst. Man. Mary
506 Staff David
505 Driver Kyle

VBA code in a standard code module;

Sub Test()
    Dim objConn As Object, strArgs As String, strSQL As String, myFile As String
    
    myFile = ThisWorkbook.FullName
    
    Set objConn = CreateObject("ADODB.Connection")
    
    strArgs = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)}; Readonly=False; DBQ=" & myFile
    objConn.Open strArgs
    
    strSQL = " Update [" & myFile & "].[Sheet1$] As T1 " & _
             " Left Join " & _
             " [Sheet2$] As T2 " & _
             " On T1.[ID] = T2.[ID] " & _
             " Set T1.[POSITION] = T2.[POSITION] "
    
    objConn.Execute (strSQL)
    objConn.Close

    Set objConn = Nothing
End Sub

Code is written and tested using Excel 2010 64 Bit on Win 11 64 Bit

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166126

You can use VLOOKUP here:

Sub Tester()
    Dim wb As Workbook, firstWs As Worksheet, secondWs As Worksheet
    Dim res, v, i As Long
    
    Set wb = ThisWorkbook
    Set firstWs = wb.Worksheets(1)
    Set secondWs = wb.Worksheets(2)
    
    Application.ScreenUpdating = False
    For i = 2 To 2000 'start at 2 to skip header
        With firstWs.Range("B" & i)
            v = .Value
            If Len(v) > 0 Then 'anything to look up?
                res = Application.VLookup(v, secondWs.Range("B2:C3000"), 2, False) 'no `.WorksheetFunction`
                If Not IsError(res) Then .Value = res 'if no match, `res` will be an error value
            End If
        End With
    Next i
    Application.ScreenUpdating = True
End Sub

Upvotes: 1

Related Questions