0 
Dim monitorRange As Range
Set monitorRange = Intersect(ws.Range("A:A,D:D,G:G,J:J,M:M,P:P,S:S"), ws.Range("16:30,42:56,68:82,95:109,122:135,147:161"))




        cell.Offset(0, 1).Value = Environ("Username")
        cell.Offset(0, 2).Value = Now
    Next cell
End If



' Set the source and target worksheets
Set wsSource = ThisWorkbook.Sheets("Sheet2")
Set wsTarget = ThisWorkbook.Sheets("PP8")

' Define the range of lookup values in the target sheet
Set lookupRange = wsTarget.Range(("B:B,E:E,H:H,K:K,N:N,Q:Q,T:T"), ws.Range("16:30,42:56,68:82,95:109,122:135,147:161"))

' Loop through each cell in the lookup range
For Each cell In lookupRange
    lookupValue = cell.Value
    
    ' Perform VLOOKUP in the source sheet
   
    result = Application.VLookup(lookupValue, wsSource.Range("A5:B17"), 2, False)
    
    
    ' Check if the lookup was successful
    If Not IsError(result) Then
        cell.Offset(0).Value = result ' Place the result in the adjacent cell
    Else
        cell.Offset(0).Value = "Not Found"
    End If
Next cell

So my first code is for entering username in cells 1, and timestamp in cells 2 after the User answers a question in the above ranges (CELL 0). I'm then trying to create a Vlookup code in VBA that uses the source sheet to replace the username in cell 1 in realtime with users last name from source sheet chart. The reason is I have about 500 questions on each of the 26 sheets.

Improve this question
13
  • 2
    Remove the On Error Resume Next and On Error GoTo 0, and remove the .WorksheetFunction. Btw the .Offset(0, 0) is redundant. Commented Apr 11 at 15:31
  • You have to take care of that only exact match returns a value. VLOOKUP cannot find partial match in a cell. Commented Apr 11 at 15:36
  • I don't want vlookup to find partial, I thought by using False it would look for exact. Commented Apr 11 at 17:13
  • 1
    Why not put the vlookup in that code ? Commented Apr 11 at 18:48
  • 2
    Please update your post to show the full procedure. Commented Apr 11 at 22:04
Related questions
651
How can I avoid using Select in Excel VBA?
0
VBA Vlookup for multiple cells or Range
0
Object Not found doing VBA Vlookup
Load 6 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.