Macro to find data from cell and replace data on different sheet

1
votes

I'm after a hopefully simple Macro/VBA code for my sheet i am using, i have searched far and wide but my VBA knowledge is quite limited so am not sure what the coding is doing in other answers.

Basically i have two sheets, Sheet2 has two columns, Column A has a number in it i.e. 2158 and column B has a name in it. What i want this macro to do is in Sheet1 i want to type in a number in Cell A1 and a name in cell B1 and then the macro finds that number used in Sheet1 Cell A1 in sheet 2 and then replaces the name from Sheet1 B1 with the correpsonding name in sheet2?

Hope that makes sense!!

Thanks

Daniel

2
vbaexcelexcel-2010

2 Answers

0
votes

If you don't do anything with the name in sheet1!b1, you can pull the corresponding value from sheet2!b1 to sheet1!b1 using the VLOOKUP function.

In Sheet1 Cell B1, enter the formula below. =VLOOKUP(A1,Sheet2!A:B,2,FALSE)

Then you can enter your number on sheet1!A1, the corresponding value from sheet2!b2 should display on sheet1!b1. If there's no matching found, you will get the #N/A.

0
votes

Give this a try:

Sub dural()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    v1 = s1.Range("A1")
    v2 = s1.Range("B1")
    s2.Activate
    For Each r In Intersect(ActiveSheet.UsedRange, Range("A:A"))
        If r.Value = v1 Then
            r.Offset(0, 1).Value = v2
        End If
    Next
End Sub