| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture match and update EXCEL 2003
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. Heres 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 Ive 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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Capture match and update EXCEL 2003
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 --------------- Jacob Skaria "scmcdowell" wrote: 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. Heres 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 Ive 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Capture match and update EXCEL 2003
Worked Like a champ. Thank you so much. Steve
"Jacob Skaria" wrote: 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 --------------- Jacob Skaria "scmcdowell" wrote: 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. Heres 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 Ive 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Excel screen capture to capture cells and row and column headings | Excel Discussion (Misc queries) | |||
| Capture Update Links event VBA | Excel Programming | |||
| Excel 2003 - Update or Don't Update Links Problem | Excel Programming | |||
| capture DDE update value in VBA macro | Excel Programming | |||
| capture DDE update value in VBA macro | Excel Discussion (Misc queries) | |||
Hybrid Mode
