VBA to Finds x Value in Column A:A, writes y Value in Column B:B

[sanantonio]

Hi All,

After some help again! Thanks in advance to those who could help out

To skip the long-winded explanation and get right down to the issue I've created a dummy excel to explain, if we can get it to work on the dummy I'll be able to adapt it to the genuine article.

When the user opens the excel, it will capture their username in Sheet 3 cell A1 (Shown below). In our example the username is "User 1".

User will then navigate to Sheet 2, and select an ID which will take them to the relevant sheet for that ID (In this example Sheet 1)(In real thing I've just hyperlinked this to the ID)

Below is Sheet 1, the ID for the data below will display in cell A1.

Once the user has done what they need to do in Sheet 1, they need to hit the "Flag" button which I need the macro for...

When the button is pressed the macro will take the value from Sheet 3 cell A1, and then it will find the relevant ID (Found in Sheet1.A1), in Column A of Sheet 2 and put the Sheet 3 cell A1 value in Column B.
So in our example for ID 123 (Sheet 1.A1), the user would hit the button and it would write "User 1" (From Sheet3.A1) into cell (Sheet2). B2. It writes it in B2 because it's searched for the ID in column A and knows to write the username in Column B.

Hope this makes sense! Can anyone help me out?

 

[Sam_D_Ben]

Something like this,

Sub FlagUser()
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Dim searchID As String, userID As String
    Dim foundCell As Range

    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")
    Set ws3 = Sheets("Sheet3")

    searchID = ws1.Range("A1").Value
    userID = ws3.Range("A1").Value

    If searchID = "" Then Exit Sub

    Set foundCell = ws2.Range("A:A").Find(searchID, LookAt:=xlWhole)

    If Not foundCell Is Nothing Then ws2.Cells(foundCell.Row, 2).Value = userID
End Sub

 

[Fuji]

1) To a standard code module.

Sub DoIt()
    Dim x
    x = [match(a1,sheet2!a:a,0)]
    If IsNumeric(x) Then [Sheet2].Cells(x, "b") = [sheet3!a1]
End Sub

2) Assign "DoIt" to all the buttons in the relevant sheets.

 

[sanantonio]

Hi @Fuji @Sam_D_Ben

Both these solutions work thank you very much!