VBA Code: Update Row

[dramqueenuk]

I have data being submitted on a sheet called, "Overview".
I am wanting to use VBA to find the corresponding user name and update columns J and V with the new data.

This code ran the first time correctly.
The 2nd time, it put the data in columns K and W, not J and V.
The 3rd time, it gives a 'Range' of object '_Global' failed error and continues to do so on the first Fnd.Offset row.

What have I done wrong?

I'd also like to add conditional formatting so that when this data is submitted, the whole row changes to grey in colour, but the code I've tried so far isn't working, so if anyone has a suggestion for that, it'd be really helpful!

Sub Offline_VNHO_Form()
   Dim Fnd As Range
      
   Set Fnd = Sheets("Dashboard").Range("A:A").Find(Range("User_ID_Offline_VNHO").Value, , , xlWhole, , , False, , False)
   If Fnd Is Nothing Then
      MsgBox Range("User_ID_Offline_VNHO").Value & " not found"
      Exit Sub
   End If
   Fnd.Offset(, 10).Value = Range("Offline_VNHO_Date").Value
   Fnd.Offset(, 22).Value = Range("Offline_VNHO_Reason").Value
            
End Sub

 

[offthelip]

try changing

Fnd.Offset(, 10).Value = Range("Offline_VNHO_Date").Value
   Fnd.Offset(, 22).Value = Range("Offline_VNHO_Reason").Value

to

rowno = fnd.Row
Range(Cells(rowno, 10), Cells(rowno, 10)).Value = Range("Offline_VNHO_Date").Value
Range(Cells(rowno, 22), Cells(rowno, 22)) = Range("Offline_VNHO_Reason").Value

 

[dramqueenuk]

Hi offthelip. Sorry for the delay in replying - I had to work on something else today.

I tried changing the code as you suggested. I'm now getting the same error in a different place:

Any other suggestions?

 

[offthelip]

If you are getting an error on that line I would suggest that your reference to the named range
"User_ID_Offline_VNHO"
Is incorrect,
There is nothing that I can see which is wrong with that line.

 

[dramqueenuk]

Hmmm...unless I'm looking at the wrong thing, it definitely seems to match the name I've given it:

It's not too long a name? Like there's not a character limit or something?

 

[dramqueenuk]

I closed it and reopened the spreadsheet. Now when I hit run, it doesn't give me any errors, but it also doesn't put the information in the "Dashboard" sheet. It's just like nothing happens at all.

With your code, is there a reason that .value is in the first range row before the =, but not the 2nd one?

 

[offthelip]

You haven't specified what is on what sheet and you hadn't specified you wanted it on the dashboard sheet. Also it would appear that your column A is blank.
Can you describe in words what you are trying to do

 

[dramqueenuk]

I thought if the cells were named, you didn't need to specify the sheet. That's worked for other code elsewhere.

Here's what's to happen:
1. User enters user ID in a cell named "User_ID_Offline_VNHO" on a sheet called, "Overview". They also enter the date in a cell named "Offline_VNHO_Date" and the reason in a cell named "Offline_VNHO_Reason", both on the same "Overview" sheet.
2. The user clicks on a button that says "Search". This button is associated to the macro currently being written.
3. The code looks in column A on the sheet called, "Dashboard" to see if the it can find the user ID that's in the "User_ID_Offline_VNHO" cell in the 'Overview' sheet. We don't know exactly how many rows it will need to search - it should search all rows until it finds a match.
4. If no match is found, a pop up box should appear while the user is still on the 'Overview' sheet that says the user ID hasn't been found. When the user clicks 'OK' or 'Close', the VBA code should come to an end.
5. If a match is found, these steps should be taken:
- the data in the "Offline_VNHO_Date" cell on the 'Overview' sheet should be added to column J in the matching row on the 'Dashboard' sheet.
- the data in the "Offline_VNHO_Reason" cell on the 'Overview' sheet should be added to column V in the matching row on the 'Dashboard' sheet.
6. It would be good if we could then empty the three cells on the 'Overview' sheet where data has been added by the user ("User_ID_Offline_VNHO", "Offline_VNHO_Date", and "Offline_VNHO_Reason")

Hopefully that makes it clearer for you.

 

[offthelip]

try this code:

Sub test()
With Worksheets("overview")
UserId = UCase(Range("User_ID_Offline_VNHO"))
Vdate = Range("Offline_VNHO_Date")
VReason = Range("Offline_VNHO_Reason")
End With
fnd = False
With Worksheets("Dashboard")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
namarr = Range(.Cells(1, 1), .Cells(lastrow, 1))
For i = 1 To lastrow
 If UserId = UCase(namarr(i, 1)) Then
  Range(.Cells(i, 10), .Cells(i, 10)) = Vdate
  Range(.Cells(i, 22), .Cells(i, 22)) = VReason
  fnd = True
  Exit For
 End If
Next i
End With
If Not (fnd) Then
 MsgBox (UserId & " Not Found")
Else
With Worksheets("overview")
Range("User_ID_Offline_VNHO") = ""
Range("Offline_VNHO_Date") = ""
Range("Offline_VNHO_Reason") = ""
End With
End If

End Sub

 

[dramqueenuk]

Sorry for the very delayed response. I must have missed the notification that you'd replied. You must have thought me very rude!

I copied and pasted your code and it looks like it's working. Thanks for your help!