Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Offset from an Application.Goto cell = Date or Time
Near the bottom of the code is a Application.Goto monthID statement. The offset lines shown there work but I need to do the following.
I want to offset from the GoTo cell: 1 column to the right and the first empty cell below row 5 = Time In another macro I need to offset from the GoTo cell: 1 column left and the first empty cell below row 5 = Timee 2 columns left and the first empty cell below row 5 = Date I am pretty sure if I see how any one of the offsets work, I can adapt the others. I can offset from the GoTo cell but I can't find how to make it dynamic AND add Date/Time Thanks. Howard Sub Scan_Out_Check() Dim scanIDout As Range Dim eIDout As String Dim monthID As Range Dim LrUIo As Long Dim LrMonth As Long Dim sMsg$ eIDout = Sheets("UI").Cells(5, 10) LrUIo = Sheets("UI").Cells(Rows.Count, "D").End(xlUp).Row Set scanIDout = Sheets("UI").Range("D18:D" & LrUIo).Find(What:=eIDout, _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not scanIDout Is Nothing Then scanIDout.Offset(, -2).Resize(1, 3).Copy Range("I" & Rows.Count).End(xlUp)(2) scanIDout.Offset(, -2).Resize(1, 3).ClearContents 'Sheets(Format(Date, "mmm yy")).Cells(Rows.Count, "D").End(xlUp)(2) = Time Set monthID = Sheets(Format(Date, "mmm yy")).Range("C2:BH2").Find(What:=eIDout, _ LookIn:=xlValues, _ LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, MatchCase:=False, _ SearchFormat:=False) If Not monthID Is Nothing Then Application.Goto monthID 'Application.Goto monthID.Offset(4, 1) 'Application.Goto monthID.Offset(25, 0) Else sMsg = "No match found!" sMsg = sMsg & vbLf & vbLf sMsg = sMsg & "Some text here." MsgBox sMsg, vbExclamation End If End If Sort_In_Scan End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Offset from an Application.Goto cell = Date or Time
Near the bottom of the code is a Application.Goto monthID statement.
The offset lines shown there work but I need to do the following. I want to offset from the GoTo cell: 1 column to the right and the first empty cell below row 5 = Time Will the offset column contain blanks between row5 and last row of data? In another macro I need to offset from the GoTo cell: 1 column left and the first empty cell below row 5 = Timee 2 columns left and the first empty cell below row 5 = Date If this is the same project you posted a link to in your other thread titled "Chaneg_Event target either of two cells" then can we assume both macros are called from the same event? If so then both events share the same ref to the 'found' range object. Perhaps these macros need an arg so the ref is common to the caller and any called procedures... In the calling event: Dim rngFound ... Call Scan_In_Check(rngFound) or Call Scan_Out_Check(rngFound) ... In the called events: (Scan_Out_Check for example) Use rngFound instead of the local variable 'monthID' Replace Set monthID = Sheets(... with Set rngFound = Sheets(... OR leave as is and add... If Not monthID Is Nothing Then Set rngFound = monthID ...where the header for called macros using the ref is... Sub MyMacro(rng As Range) ...which *only* get called *If Not rngFound Is Nothing* so your Scan...Check macros can pass a fully qualified ref back to the caller which can then pass allong to 'MyMacro' like this... If Not rngFound Is Nothing Then Call MyMacro(rngFound) OR use the ref for its own purposes... If Not rngFound Is Nothing Then... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Offset from an Application.Goto cell = Date or Time
On Tuesday, June 3, 2014 10:42:41 AM UTC-7, GS wrote:
Near the bottom of the code is a Application.Goto monthID statement. The offset lines shown there work but I need to do the following. I want to offset from the GoTo cell: 1 column to the right and the first empty cell below row 5 = Time Will the offset column contain blanks between row5 and last row of data? In another macro I need to offset from the GoTo cell: 1 column left and the first empty cell below row 5 = Timee 2 columns left and the first empty cell below row 5 = Date If this is the same project you posted a link to in your other thread titled "Chaneg_Event target either of two cells" then can we assume both macros are called from the same event? If so then both events share the same ref to the 'found' range object. Perhaps these macros need an arg so the ref is common to the caller and any called procedures... In the calling event: Dim rngFound ... Call Scan_In_Check(rngFound) or Call Scan_Out_Check(rngFound) ... In the called events: (Scan_Out_Check for example) Use rngFound instead of the local variable 'monthID' Replace Set monthID = Sheets(... with Set rngFound = Sheets(... OR leave as is and add... If Not monthID Is Nothing Then Set rngFound = monthID ..where the header for called macros using the ref is... Sub MyMacro(rng As Range) ..which *only* get called *If Not rngFound Is Nothing* so your Scan...Check macros can pass a fully qualified ref back to the caller which can then pass allong to 'MyMacro' like this... If Not rngFound Is Nothing Then Call MyMacro(rngFound) OR use the ref for its own purposes... If Not rngFound Is Nothing Then... Hi Garry, Will the offset column contain blanks between row5 and last row of data? There will be no blanks. Yes, it is the same project. I'll copy you advice to the sheet and see if I can blend it in. May make more sense to me there. I think you are suggesting reducing the many different 'found' names. I have tried to use names that refer to stuff in the code or worksheet to help me keep thing clear. Reducing that aspect by coming from one of the called macros would be nice, IF that is what you mean and IF I can persuade it to work for me. Thanks for looking in. Howard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Offset from an Application.Goto cell = Date or Time
It appears to me that this project is being designed by way of its
'evolution'. While this serves a purpose to a piont, I can clearly see by looking at the file there's a serious need for restructuring as well as basic worksheet design. (Not criticizing what's been done to date. Just reiterating that, as you already know, I'd go about this task differently!<g) IMO, Scan_Out_Check should not have any hard-coded refs, but rather contain args that the caller can pass. This will make the Scan...Check routines reusable by any caller in the project. (Assuming there could *possibly* be other sheets using event code for the same purpose!) Also, since these are 'checking' routines, it makes sense to me to convert them to functions that return a Boolean result that can be used like this... If bScan_In_Check(rngFound, rngCheck) Then... OR If bScan_Out_Check(rngFound, rngCheck) Then... ...so their function is to verify if Scan_In or Scan_Out did occur When UI!$B$5 or UI!$J$5 changed. This obviates the need to check "If Not rngFound Is Nothing" because the return from the Scan...Check functions already does that. It's completely possible, then, that only 1 Check_ScanInOut routine is needed... Function bCheck_ScanInOut(rngFound, rngTarget As Range, Criteria) As Boolean Set rngFound = rngTarget.Find(What:=Criteria, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, _ SearchDirection:=xlNext, MatchCase:=False) bCheck_ScanInOut = (Not rngFound Is Nothing) End Function Also, the event should redirect execution to an external routine that handles processing of the business logic. You already do this with Scan_In_Check and Scan_Out_Check so the rewrite of Scan_Out_Check might go this way... Sub Scan_Out_Check() Dim scanIDout As Range, rngTarget As Range, monthID As Range Dim LrUIo&, LrMonth&, eIDout$, sMsg$ eIDout = Sheets("UI").Cells(5, 10) LrUIo = Sheets("UI").Cells(Rows.Count, "D").End(xlUp).Row Set rngTarget = Sheets("UI").Range("D18:D" & LrUIo) If bCheck_ScanInOut(scanIDout, rngTarget, eIDout) Then With scanIDout.Offset(, -2).Resize(1, 3) .Copy Range("I" & Rows.Count).End(xlUp)(2): .ClearContents End With 'scanIDout Set rngTarget = Sheets(Format(Date, "mmm yy")).Range("C2:BH2") If bCheck_ScanInOut(monthID, rngTarget, eIDout) Then With monthID '... End With 'monthID Else sMsg = "No match found!" sMsg = sMsg & vbLf & vbLf sMsg = sMsg & "Some text here." MsgBox sMsg, vbExclamation End If 'bCheck_ScanInOut(monthID End If 'bCheck_ScanInOut(scanIDout Sort_In_Scan End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Offset from an Application.Goto cell = Date or Time
Hi Garry,
I installed the function in a standard module. This works to enter the Scan Out time offset from the Application.Goto monthID line on Jun 14 sheet. It does not evoke the message box code if there is no match, does not error, just does nothing. Also the focus does not go back to the change_event code which should re-select B5 as the active cell. Cell remains B6 From here I am lost on the Scan_In which must: Check on sheet UI, column D first, if there msgbox "already signed in" Then on sheet UI, column K next, if there copy to column D and clear contents Then if not in either above go to sheet DATABASE and copy to sheet UI, column D. Howard Private Sub Worksheet_Change(ByVal Target As Range) '/ garry If Intersect(Target, Range("B5,J5")) Is Nothing Then Exit Sub If Target.Count = 1 Then Application.EnableEvents = False On Error Resume Next Select Case Target.Address Case "$B$5": Call Scan_In_Check Target.Activate Case "$J$5": Call Scan_Out_Check Target.Activate End Select Application.EnableEvents = True End If End Sub Sub Scan_Out_Check() Dim scanIDout As Range, rngTarget As Range, monthID As Range Dim LrUIo&, LrMonth&, eIDout$, sMsg$ eIDout = Sheets("UI").Cells(5, 10) LrUIo = Sheets("UI").Cells(Rows.Count, "D").End(xlUp).Row Set rngTarget = Sheets("UI").Range("D18:D" & LrUIo) If bCheck_ScanInOut(scanIDout, rngTarget, eIDout) Then With scanIDout.Offset(, -2).Resize(1, 3) .Copy Range("I" & Rows.Count).End(xlUp)(2): .ClearContents End With 'scanIDout Set rngTarget = Sheets(Format(Date, "mmm yy")).Range("C2:BH2") If bCheck_ScanInOut(monthID, rngTarget, eIDout) Then Application.Goto monthID With monthID .Offset(25, 1).End(xlUp)(2) = Time End With 'monthID Else sMsg = "No match found!" sMsg = sMsg & vbLf & vbLf sMsg = sMsg & "Some text here." MsgBox sMsg, vbExclamation End If 'bCheck_ScanInOut(monthID End If 'bCheck_ScanInOut(scanIDout Sort_In_Scan End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Offset from an Application.Goto cell = Date or Time
I deliberately removed the GoTo line because it's not needed AND so you
only need to Target.Select once at the top of code in the 'If Target.Count = 1' block! Why do you think the GoTo is necessary? The MsgBox displays if eIDout is not found by bCheck_ScanInOut when testing on a dummy sheet named "Jun 14". This is the 'Else' part of the 2nd If. Did you want that the MsgBox should display when either If returns 'False'? If so then that's not a problem using a var to store a result message (sResultMsg$) and a result boolean (bSuccess) so users are notified in context I deliberately left modifying Scan_In_Check to you (using Scan_Out_Check as an example for calling bCheck_ScanInOut) purely as an exercise. This followed some rather difficult suppression of my urge to rewrite the version I currently have. I resolved that if you had difficulty with rewriting it yourself then I'd ask you to email me a copy of your latest version of this project so I'm working with real data. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Offset from an Application.Goto cell = Date or Time
I deliberately left modifying Scan_In_Check to you (using Scan_Out_Check as an example for calling bCheck_ScanInOut) purely as an exercise. This followed some rather difficult suppression of my urge to rewrite the version I currently have. I resolved that if you had difficulty with rewriting it yourself then I'd ask you to email me a copy of your latest version of this project so I'm working with real data. -- Garry I believe I can adapt Scan_In_Check if you could make Sub Scan_Out_Check() a fully operating macro that will remove the data from the Sheet UI column D to column K (which it already does) and include a working replacement for GoTo. The scan out time column on Jun 14 sheet for each employee ID starts 1 column right and the first empty cell below row five. (there are no blanks in that column, row 6 to row 1.4 mill. I think the change_event code needs some tweaking if I read you reply correctly. That would be helpful also. If that proves too much for me to digest and implement then I'll scrap the project. Howard |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Offset from an Application.Goto cell = Date or Time
I believe I can adapt Scan_In_Check if you could make Sub
Scan_Out_Check() a fully operating macro that will remove the data from the Sheet UI column D to column K (which it already does) and include a working replacement for GoTo. There is no 'working replacement' for GoTo! You have not explained why it needs to be there and so I excluded it because it has no bearing on code being executed. The scan out time column on Jun 14 sheet for each employee ID starts 1 column right and the first empty cell below row five. (there are no blanks in that column, row 6 to row 1.4 mill. The file download you linked to has headings in row4 on 'Jun 14', and data starts in row5. Regardless, a better design wouldn't need a row number as new data would be inserted at the next row below existing data (normally speaking). This is why I suggested a restructure of the project's design. Month sheets appear to be identical and so should be inserted using a 'master' template, when/as needed. It should implement local scope named ranges which lists (col data) should be dynamic, and positions (col headers) should be col-absolute/row-relative. Code can ref this without using hard=coded addresses or target sheetnames. IMO, this is a 3-sheet core project that should be able to generate month sheets as/when needed. (Though I don't see here why it can't use year sheets with month groupings) I think the change_event code needs some tweaking if I read you reply correctly. That would be helpful also. The project file I have doesn't have a change_event. Also, Sub MyCheck is in the ThisWorkbook component and Module1 is empty. What would really be helpful is if we both are working with the same version of the project! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Offset from an Application.Goto cell = Date or Time
Hi Howard,
Am Tue, 3 Jun 2014 22:24:52 -0700 (PDT) schrieb L. Howard: If that proves too much for me to digest and implement then I'll scrap the project. please have a look: https://onedrive.live.com/?cid=9378A...121822A3%21326 for "EMPLOYEE Tracking Template" Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Offset from an Application.Goto cell = Date or Time
Hi Howard,
Am Wed, 4 Jun 2014 18:48:34 +0200 schrieb Claus Busch: please have a look: https://onedrive.live.com/?cid=9378A...121822A3%21326 for "EMPLOYEE Tracking Template" in the monthly sheet you can't work with find. That will find the first value. But you have to look for the last. Therefore I loop from last row to 5 to look for the ID Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Offset from an Application.Goto cell = Date or Time
in the monthly sheet you can't work with find. That will find the first value. But you have to look for the last. Therefore I loop from last row to 5 to look for the ID Regards Claus B. Hi Claus, I see. That really seems to do the trick. I think I was misguided by FIND where it is used to find values across multiple sheets and count them or alter them. I revisited that example now see it does just as you say, first value only. Thanks Claus, looks like that should do it. Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Application.Union(c.Offset(0, 1), c.Offset(0, 2)... to a list onsame & another sheet | Excel Programming | |||
Dynamic Reference Cell in Offset Formula | Excel Discussion (Misc queries) | |||
Place a Dynamic Date + a Certain Time in a Cell | Excel Worksheet Functions | |||
Goto a dynamic cell address within a macro | Excel Discussion (Misc queries) | |||
Goto a dynamic cell address within a macro | Excel Programming |