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!

 

[Fluff]

Try

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

 

[dramqueenuk]

Thanks for the suggestion, Fluff.
I tried changing that bit of code and then ran the macro. I got an "invalid qualifier" error. Any other suggestions?

 

[Fluff]

What is User_ID_Off ?

 

[dramqueenuk]

I noticed that I'd missed out "Off" from the original code myself and updated it. I couldn't see a way to edit my original post, but this is the code I now have:

Sub Offboarding_Form_Submission()
Dim wb As Workbook
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim lastRow As Long


Set ws = ThisWorkbook.Worksheets("Offboarding_Form")
Set ws2 = ThisWorkbook.Worksheets("Dashboard")


Dim User_ID_Off As String
Dim Offboarding_Date As String

lastRow = wb.ws2.Cells(Rows.Count, 1).End(xlUp).Row
User_ID_Off = wb.ws.Cells(3, 4)

For i = 2 To lastRow
If Range(wb.ws2.Cells(i, 1)).Value = User_ID_Off Then
wb.ws2.Cells(i, 17).Value = Range("Offboarding_Date").Value

Else
Range("E3").Value = "User not found"

End If
Next

End Sub

I've added in an Else condition as I want to let the user know there's not been a match found.

The error I'm now getting is on the lasRow= line. The error says, "Run time error '91': Object variable or With block variable not set.

 

[Fluff]

Whilst you have declared wb you haven't assigned anything to it, hence the error.
That said you don't need, just use

lastRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row

 

[dramqueenuk]

OK, I still couldn't get that to work. Kept giving an error. I changed the code to the below based on other articles I read. When I run it, I don't get an error anymore, but even if I put in a User ID that is in the 'Dashboard' sheet, it's still popping up with the 'user not found' box, which makes me think that it's either not searching every row or that there's some issue with a format or trim or something that would be causing a mismatch. Any ideas?

Sub Offboarding_Form_Submission()

Dim lastrow As Long, x As Long

Dim User_ID_Off As String
Dim Offboarding_Date As String

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

For x = 2 To lastrow
If Sheets("Dashboard").Cells(x, 1).Value = "User_ID_Off" Then
Sheets("Dashboard").Range(x, 17).Value = Cells("Offboarding_Date").Value

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

End If
End If
Next
End Sub

 

[Fluff]

Can you please answer my question in post#4?

 

[dramqueenuk]

"User_ID_Off" is the name of a cell where the user enters a user ID number. This number is what the VBA code needs to look for and find a match to in the other worksheet ("Dashboard")

 

[Fluff]

Will that number exist once in the sheet, or multiple times?