VBA Match Field & Update Row

dramqueenuk

New Member
Joined
Sep 22, 2020
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
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:
1600791640589.png


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:
2020-09-22_17h17_59.png


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


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!
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,599
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
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

New Member
Joined
Sep 22, 2020
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,599
Office Version
  1. 365
Platform
  1. Windows
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
VBA Code:
   Set Fnd = Sheets("Dashboard").Range("A:A").Find(Range("User_ID_Off").Value, lookat:=xlWhole, MatchCase:=False, SearchFormat:=False)
 

dramqueenuk

New Member
Joined
Sep 22, 2020
Messages
21
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thanks a lot.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,599
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

dramqueenuk

New Member
Joined
Sep 22, 2020
Messages
21
Office Version
  1. 2016
Platform
  1. Windows
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?

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,599
Office Version
  1. 365
Platform
  1. Windows
As this is a totally different question, you will need to start a new thread. Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,118,189
Messages
5,570,760
Members
412,340
Latest member
nikitesh95
Top