VBA Match Field & Update Row

[dramqueenuk]

I have created a tab for adding data to my dashboard to make it easier for the users who're not used to using to Excel to ensure the correct data is entered.

This is where the code is entered:

When they click on 'Submit', the data is added to the sheet titled "Dashboard". The VBA code for this is:

Sub NHO_Dashboard_Form_Submission()

ws_output = "Dashboard"

next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row

Sheets(ws_output).Cells(next_row, 1).Value = Range("User_ID").Value
Sheets(ws_output).Cells(next_row, 2).Value = Range("Business").Value
Sheets(ws_output).Cells(next_row, 3).Value = Range("Site").Value
Sheets(ws_output).Cells(next_row, 4).Value = Range("Surname").Value
Sheets(ws_output).Cells(next_row, 5).Value = Range("First_Name").Value
Sheets(ws_output).Cells(next_row, 9).Value = Range("Day_1_Date").Value

End Sub

This works correctly.

I then need to have a tab for updating the data. The user will enter the user ID in the sheet titled "Offboarding Form", which is a unique number found in column A(1) of the dashboard. I need the VBA code to search for a row that holds that user ID (there will only ever be 1 matching row). Once the match is found, it needs to input data from cell D5 ("Offboarded_Date") from here:

To column Q of the dashboard, which is on the sheet titled "Dashboard":

I looked at some videos on YouTube and it seemed like it was saying the code I needed to perform this action is this:

Private Sub Offboarding_Form_Submission()

Dim User_ID As String

User_ID = Trim(User_ID_Off.Text)

lastrow = Worksheets("Dashboard").Cells(Rows.Count, 1).End(xlUp).Row


For i = 2 To lastrow

If Worksheets("Dashboard").Cells(i, 1).Value = User_ID_Off Then

Worksheets("Dashboard").Cells(i, 17).Value = Offboarding_Date.Text


End If

Next

End Sub

However, when I try this by clicking on 'Submit', nothing happens. Can anyone help me identify what the correct code should be?

I do have a test Excel workbook I could share, but I can't see how I would attach that to this thread!

 

[dramqueenuk]

It will only appear once.

 

[Fluff]

Ok, how about

Sub dramqueenuk()
   Dim Fnd As Range
      
   Set Fnd = Sheets("Dashboard").Range("A:A").Find(Range("User_ID_Off").Value, , , xlWhole, , , False, , False)
   If Fnd Is Nothing Then
      MsgBox Range("User_ID_Off").Value & " not found"
      Exit Sub
   End If
   Fnd.Offset(, 17).Value = Range("Offboarding_Date").Value
End Sub

 

[dramqueenuk]

Thank you, Fluff, for all your help. That seems to have worked perfectly. Could I ask you to explain the 'Set Fnd' line to me from 'Value' onwards so that I can re-use it in future in other places where I need to do the same thing? The rest of it I understand and know what has to go where.

 

[Fluff]

Range.Find has various arguments which can be found here Range.Find method (Excel)

As long as the arguments are in the correct order you do not need to use the name of the argument. So that line could be written

   Set Fnd = Sheets("Dashboard").Range("A:A").Find(Range("User_ID_Off").Value, lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)

 

[dramqueenuk]

Thanks a lot.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[dramqueenuk]

Hi Fluff. I'm working on another part of the same document where I want the user to be able to enter the user ID then click on search. This would be entered in the sheet called, "Overview" in a cell named "User_ID_Search". The VBA should look on the sheet called, "Dashboard" and find the matching user ID in column A. It should then copy certain pieces of data from that row and place them in a table in the "Overview" sheet. This is the code I had tried, but it's not working - the error is on the 'Fnd.Offset' row. Any ideas how I can fix it?

Sub SearchUser()
Dim Fnd As Range

Set Fnd = Sheets("Dashboard").Range("A:A").Find(Range("User_ID_Off").Value, , , xlWhole, , , False, , False)
If Fnd Is Nothing Then
 MsgBox Range("User_ID_Search").Value & " not found"
 Exit Sub
 End If
 
Fnd.Offset(Sheets("Dashboard").Cells(x, 1) = Sheets("Overview").Range("User_ID_Search")
Sheets("Overview").Range("B18") = Sheets("Dashboard").Cells(x, 1)
Sheets("Overview").Range("C18") = Sheets("Dashboard").Cells(x, 9)
Sheets("Overview").Range("D18") = Sheets("Dashboard").Cells(x, 13)
Sheets("Overview").Range("E18") = Sheets("Dashboard").Cells(x, 16)
Sheets("Overview").Range("F18") = Sheets("Dashboard").Cells(x, 15)

Else
response = MsgBox("User not found", vbExclamation)
  If response = vbOK Then
  Exit Sub
  End If
End If

End Sub

 

[Fluff]

As this is a totally different question, you will need to start a new thread. Thanks