Help for Excel time format "MACRO"

[vijay]

i am working with a organisation.where i need to work on a Excel Sheet daily.
It would be helpful if you get me done this, as it would save my lot of time
and would decrease my work load.
Problem as follow. Version (Excel 2003)
A B C D E F G H I J K L M N
1 I don't want macro to work on first 5 rows
2 I don't want macro to work on first 5 rows
3 I don't want macro to work on first 5 rows
4 I don't want macro to work on first 5 rows
5 I don't want macro to work on first 5 rows
6 A B C D E F G H I J K L M N
7 ST ET Y Z
8 ST ET Y Z
9 ST ET Y Z

The above A,B,C, are the Column, and to the right side rows with numbers.
Daily i do no. of file, which needs to be update in the Excel.
In column "F7" i have keyed "ST" that is equal to start time, before
starting with each new file i need to key the Start time in column "F", and
after completion of that file i need to key end time in column "G". In Column
"G7" ED stands for end time. Rest of the column are having other details that
i need to fill in.
Daily i do huge no. of files, for which i need to key the time everynow and
then.
What i want is whenever i Key "Y"=(file name) in column "I7" the cell in
"F7" should automatically pickup the current time of the system as "Start
Time". And after completing of that file, when i key "Z"=(file name) in
column "K7" the cell in "G7" should automatically pickup the current time of
the system as "End Time".
I don't want to use shortcut keys for the current time every time.
It would be helpful if you get MACRO written for me on this.

 

[Tom Hutchins]

Right-click on the sheet tab of your worksheet and select 'View Code'. This
opens the Visual basic Editor (VBE). Paste the following code in the big
blank window:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 6 Then Exit Sub
Select Case Target.Column
Case 9: 'column I
If UCase(Left(Target.Value, 1)) = "Y" Then
Cells(Target.Row, 6).Value = Time
End If
Case 11: 'column K
If UCase(Left(Target.Value, 1)) = "Z" Then
Cells(Target.Row, 7).Value = Time
End If
End Select
End Sub

Select File >> Save, then File >> Close to close the VBE and return to
normal Excel. The time will be stamped in column F on the same row when you
enter anything beginning with Y in column I. The time will be stamped in
column G on the same row when you enter anything beginning with Z in column K.

If you are new to macros, this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Hope this helps,

Hutch

 

[vijay]

Hi,
Hutch

Thanks for that,But my problem is, whatever i wrote in below was an example
i do not key only "Y" in column "I", i need to key different file name such
as(MARNOK_CHAREO_2_000233), and each and every file are different from each
other.
so now it does't show any time in column "F" when i key/paste the above
file, also it is same for Column "K" i do not key only "Z" in that column.
I would be thankful if u help me with this as well.

 

[Tom Hutchins]

Okay, try this code instead:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 6 Then Exit Sub
Select Case Target.Column
Case 9: 'column I
If Len(Target.Value) > 0 Then
Cells(Target.Row, 6).Value = Time
End If
Case 11: 'column K
If Len(Target.Value) > 0 Then
Cells(Target.Row, 7).Value = Time
End If
End Select
End Sub

This will put the current time in F or G when you enter anything in I or K.

Hope this helps,

Hutch

 

[vijay]

Hutch,

Thanks for that Tom. It would really save lot of my time. Frankly speaking i
don't no anything about MACRO, but i am very much interested to learn it .
Can u suggest me on that what is the book that i need to refer. Anyway now i
am asking more from u. But really thanks again for your help.

 

[Tom Hutchins]

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's book "Excel 2003 Power Programming with VBA" is very good.

There are great online resources also for learning VBA. Here are a few:
www.cpearson.com/excel.htm
www.rondebruin.nl/
www.xldynamic.com/source/xld.html
www.contextures.com/
www.dicks-blog.com
www.mcgimpsey.com/

and don't forget http://www.mvps.org/dmcritchie/excel/getstarted.htm

or the link I gave you already:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Hope this helps,

Hutch

 

[vijay]

Tom,

Thanks
I will refer to those.
--
vijay

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's book "Excel 2003 Power Programming with VBA" is very good.

There are great online resources also for learning VBA. Here are a few:
www.cpearson.com/excel.htm
www.rondebruin.nl/
www.xldynamic.com/source/xld.html
www.contextures.com/
www.dicks-blog.com
www.mcgimpsey.com/

and don't forget http://www.mvps.org/dmcritchie/excel/getstarted.htm

or the link I gave you already:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Hope this helps,

Hutch