Change/move data when cell date => today()

[archerks]

I have a time off sheet that is filled by a user form. Besides listing the actual "Vacation" taken I have added "Vac Request". I would like to be to change all the "Vac Request" rows into actual "Vacation" when the "Vac Request" start date => today() when I press a Command Button.

On a ROW BY ROW basis here is the needs to be done if a value in Col R is > 0 and the date in Col C is => the Today() then move (cut) the value in Col R to Col F and change Col Q from "Vac Request" to "Vacation".

There may be up to 3000 rows that this needs to evaluate.

Time Off 2005.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
199	Ref	Name	StartDate	Returned	Holiday	Vac	Sick	Funeral	Other	Tardy	Dept	Other	1/2Day	Mgr	Ref	Days	Type	VacReq
200	100	Startof2005	1/1/2005	1/2/2005	10.1	10.2	10.3	10.4	10.5	10.6
201	101	Schuetz,John	1/3/2005	1/4/2005		1.0					Eng					1.0	Vacation
202	102	Menold,Donald	1/7/2005	1/11/2005		2.0					Service					2.0	Vacation
203	103	Hill,Chad	1/5/2005	1/5/2005						1.0	Shop					1.0	Tardy
204	104	Duryea,Chad	1/5/2005	1/5/2005						1.0	Shop					1.0	Tardy
205	105	WilliamsII,Lloyd	1/5/2005	1/6/2005		1.0					Shop			Mgr		1.0	Vacation
206	106	Hughes,Woody	1/3/2005	1/10/2005					5.0		Shop	Disability				5.0	Other
207	107	Wilhelm,William	1/7/2005	1/10/2005		0.5					Shop		Afternoon			0.5	Vacation
208	108	Shaw,David	1/14/2005	1/18/2005							Acct		AfternoonofStartDate			1.5	VacRequest	1.5
209	109	Rockey,John	1/28/2005	2/2/2005							Shop					3.0	VacRequest	3.0
210
TimeOff

 

[Greg Truby]

Appears to work in testing:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> UpdateVacation()
    <SPAN style="color:#00007F">Dim</SPAN> rngColB <SPAN style="color:#00007F">As</SPAN> Range, rngCell <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Const</SPAN> bytOffsetBtoStart <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> = 1
    <SPAN style="color:#00007F">Const</SPAN> bytOffsetBToVac <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> = 4
    <SPAN style="color:#00007F">Const</SPAN> bytOffsetBToReq <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> = 16
    <SPAN style="color:#00007F">Set</SPAN> rngColB = Range("B1", [B65536].End(xlUp))
    
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> rngCell <SPAN style="color:#00007F">In</SPAN> rngColB
        <SPAN style="color:#00007F">If</SPAN> rngCell.Offset(, bytOffsetBtoStart) <= Date _
        And rngCell.Offset(, bytOffsetBToReq) <> 0 <SPAN style="color:#00007F">Then</SPAN>
            rngCell.Offset(, bytOffsetBToVac) = rngCell.Offset(, bytOffsetBToReq)
            rngCell.Offset(, bytOffsetBToReq).ClearContents
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> rngCell
        
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Comment: one could quite easily simply code the appropriate offsets into the calls to the .Offset() method. However, if the layout of the data ever changes, i.e. a column is added or deleted, then updating the code is harder. For future upkeep, it's easier if you define the offsets between columns as constants and reference those. That way if you insert or delete a column you simply update the constant and you're done.
HTH

 

[archerks]

Thank you....

Just added info for my Col Q and everyting works great.

Where can I find reading info about offsets and how to use them? Offsets are new (unknown to me).

Thanks again,
Dave

 

[Greg Truby]

<ul>[*]In the VBE, hit F2 to call up the object browser.[*]In the left list pane click and type "R". That should jump you to the Range object (scroll to it if for some reason it doesn't).[*]In the right list pane click and type "O". That should jump you to the Offset property.[*]Just hit F1 for the help on Offset.[/list]There is also an OFFSET() worksheet function in Excel. You can just type that into help in Excel for more info.