Dynamic Offset from an Application.Goto cell = Date or Time

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

L. Howard

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
 
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
Click to expand...

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
Click to expand...

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
 
Will the offset column contain blanks between row5 and last row of

data?







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...
Click to expand...


Hi Garry,
Will the offset column contain blanks between row5 and last row of
Click to expand...
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 andIF I can persuade it to work for me.

Thanks for looking in.

Howard
 
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
 
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
 
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
 
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.
Click to expand...


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 forGoTo. 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
 
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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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
 
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
Click to expand...
Regards

Claus B.
Click to expand...


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
 
Back
Top