Tuesday, July 19, 2011

VBA Script to Lookup Values and Return Result

This is a quick VBA script that I wrote after searching for a few hours online. I am posting it in case someone in the same dilemma I was in needs the same script. Basically, the script is for looking up values in another worksheet based on a current value. In this example, I am taking a user account name from the first sheet and then  looking up the last time that he or she logged in using the second sheet (which contains thousands of entries listing all logins). I want to grab the last occurrence of the login (most recent).

To begin, I will declare the variables:

Dim userId As String
Dim found As Range
Dim theDate As String

Next, I am going to loop through all of the cells that I want. In my example, I have 1730 user names to lookup (you can see why I wrote a script). First, it assigns the user name from the first sheet to the "userId" variable. Next, it searches through the "Logins" sheet backwards by matching the user ids (since this sheet is ordered by login date and we want the most recent). If it finds a match, it stores the cell's value in the range "found." If found is empty, it writes in the original cell that the last login date was never. If found is populated (a match was found) then it assigns the date stored in the field to the variable "theDate." Finally, it writes the value of theDate into the original sheet in Cells(i,3) meaning the 3rd column of the i^th row.

For i = 1 To 1730
  userId = Cells(i, 1).Value
  Set found = Sheets("Logins").Columns(2).Cells.Find(What:=userId, After:=[B1],
  LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
  If found Is Nothing Then
    Cells(i, 3).Value = "Never"
    theDate = Sheets("Logins").Cells(found.Row, 1).Value
    Cells(i, 3).Value = theDate
  End If
Next i

No comments:

Post a Comment