Finding cells and updating a row in another sheet

[greglowe]

Hello

Trying to set up a sheet to apply a reference to an identical record in another sheet.

on my calc sheet I have:

What I need it to do is find the ID on another sheet and update Match on that sheet.

the other sheet is laid out like this:

I can get it to work for the first record on the calc sheet but I can figure out how to have it loop through all of the values

It's just updating the same row each time...

the code is have so far is:

Sub Find_And_Update()

    Dim FindString As String
    Dim Rng As Range
    Dim i As Integer
    Dim MatchRef As String
    
    NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count
    

    FindString = Sheets("Calc Sheet(Agent)").Range("A3")
    
    For x = 1 To NumRows
    
    If Trim(FindString) <> "" Then
        With Sheets("BDX").Range("A:A")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
                ActiveCell.Offset(0, 5).Activate
           
           Else
      
            End If
        End With
    End If

    Next x

End Sub

Any help will be greatly appreciated!

 

[trunten]

See if this works for you. Just uses VLOOKUP to pull the ID across.

Sub Find_And_Update()
    Dim refSheet As Worksheet
    Dim calcSheet As Worksheet
    Dim lastRow As Long
    
    Set refSheet = Sheets("Ref") '<-- Change accordingly
    Set calcSheet = Sheets("Calc Sheet(Agent)") '<-- Change accordingly
    
    lastRow = calcSheet.Cells(calcSheet.Rows.Count, 1).End(xlUp).Row
    If lastRow > 1 Then
        With calcSheet.Range("F2:F" & lastRow)
            .Formula = "=IFERROR(VLOOKUP(A2,'" & refSheet.Name & "'!A:B,2,false),"""")"
            .Value = .Value
        End With
        MsgBox "Done :)", vbInformation
    End If
End Sub

 

[greglowe]

HI

Thank you for this!

I should have included this but the match numbers will go up in sequence depending on what's selected...

Is there a way to have the macro ignore anything in the range that already has a value?

Thanks again