Capture match and update EXCEL 2003

[scmcdowell]

I have a worksheet that I need to log the user and time into the next
available cell each time the workbook is opened. I have sheet2 with a
column(A1) of the possible logon userids and column(B1) is the associated
name. A1 is sorted.
Sheet1 has 6 columns available to store the open time and associated name.
A2:A7, F2:F7 and K2:K7 are time and B2:B7, G2:G7 and L2:L7 are for the
associated name.

I want to find the next available/empty cell and match userid on
Sheet2(A1:A30) if matched, select the name from Sheet2(B1:B30) locate next
empty time cell on Sheet1 and log the current time and the name from
Sheet2(B1:B30).

This is a post log for our supervisors. They rotate at approx. 3 hr.
intervals and open this workbook at the start of each rotation. Each log
runs from Midnight to Midnight. Here’s the layout.
A2:A7 B2:B7 F2:F7 G2:G7 K2:K7 L2:L7
IN NAME/PIN IN NAME/PIN IN NAME/PIN
0000 BREWER 15075
0130 HENDRICKS 2617
0145 BREWER 15075
0300 HOLT 2759



The columns on Sheet2 look like this:
A1:A30 B1:B30
e92121 BREWER 15075
e92123 BUSCHER 12101
e92172 HEIMBURGER 10939
e92175 HENDRICKS 2617
e92176 MCDOWELL 14677
e92177 HOLT 2759
e92178 MILLER, P. 3988
e92181 MILLER, T. 10772
e92183 HUTCHINGS 2891
e92187 JOHNSON, C. 3008
e92188 MORRISON 4088
e92189 JOHNSON, M. 14676
e92191 LABOUBE 14700

Here is the code I’ve got on workbookopen:

Sub Workbook_Open()
Range("B2").Value = Environ("UserName")
Range("A2").Value = (Time)
End Sub
I would be grateful for any ideas and help. Thanks, Steve

 

[Jacob Skaria]

Try the below macro.. Paste this in a module and call from Workbook Open
event..

Sub Workbook_Open()
UpdateLog
End Sub


Sub UpdateLog()
Dim w1 As Worksheet, ws2 As Worksheet, rngTemp As Range

Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")

If WorksheetFunction.CountIf(ws2.Range("A1:A30"), _
Environ("UserName")) = 0 Then Exit Sub

If ws1.Range("A7") = "" Then
Set rngTemp = ws1.Range("A7").End(xlUp)
ElseIf ws1.Range("F7") = "" Then
Set rngTemp = ws1.Range("F7").End(xlUp)
ElseIf ws1.Range("K7") = "" Then
Set rngTemp = ws1.Range("K7").End(xlUp)
Else
MsgBox "Filled": Exit Sub
End If

rngTemp.Offset(1, 0) = _
WorksheetFunction.VLookup(Environ("UserName"), _
ws2.Range("A1:B30"), 2, 0)
rngTemp.Offset(1, 1) = Time
End Sub

If this post helps click Yes

 

[scmcdowell]

Worked Like a champ. Thank you so much. Steve

Try the below macro.. Paste this in a module and call from Workbook Open
event..

Sub Workbook_Open()
UpdateLog
End Sub


Sub UpdateLog()
Dim w1 As Worksheet, ws2 As Worksheet, rngTemp As Range

Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set ws2 = ThisWorkbook.Sheets("Sheet2")

If WorksheetFunction.CountIf(ws2.Range("A1:A30"), _
Environ("UserName")) = 0 Then Exit Sub

If ws1.Range("A7") = "" Then
Set rngTemp = ws1.Range("A7").End(xlUp)
ElseIf ws1.Range("F7") = "" Then
Set rngTemp = ws1.Range("F7").End(xlUp)
ElseIf ws1.Range("K7") = "" Then
Set rngTemp = ws1.Range("K7").End(xlUp)
Else
MsgBox "Filled": Exit Sub
End If

rngTemp.Offset(1, 0) = _
WorksheetFunction.VLookup(Environ("UserName"), _
ws2.Range("A1:B30"), 2, 0)
rngTemp.Offset(1, 1) = Time
End Sub

If this post helps click Yes