check every cell in a range
hi all,
here i have a little problem please help me out---
i have created a macro.
i have 2 button one for start time and another one is for end time ,
right now i can click on any button and insert the end or start time but i do not want to insert end time untill start time is not there
for more clarification u can check out below code please help me out--
Dim rng As Range
Dim cell As Variant
Set rng1 = Range("L5:L65536")
Set rng = Range("K5:K65536")
For Each cell In rng
If IsEmpty(cell) Then
rng.End(xlUp).Offset(1, 0).Select
MsgBox ("enter 1 value")
Else
Sheet8.Range("L65536").End(xlUp).Offset(1, 0).Select
If Selection.row = 2 Then
Sheet8.Range("L5").FormulaR1C1 = Now()
Sheet8.Range("L5").NumberFormat = "h:mm:ss AM/PM"
Else
Selection.FormulaR1C1 = Now()
Selection.NumberFormat = "h:mm:ss AM/PM"
End If
End If
Exit Sub
Next
End Sub
thank in advance
nipendra
| Attachment | Size |
|---|---|
| Time and Motion Study Tracker111.xlsm | 648.43 KB |
check every cell in range, clarification...
OK, couple of points/questions...
1) you have 'EXIT SUB' before the line for next, so you will only process 1 cell from the 'FOR' is this intentional?
2) There is a lot of selecting... it is best to avoid this as it is slow and will effect the time your macro tables to run considerably.
3) You use 'Sheet8' a lot where it may be better to:
Dim ws as worksheet: Set ws = sheet8
With ws
''' do work
End With
4) 'If IsEmpty(cell) Then' this could do with changing to:
If Len(cell) = 0 Then
As 0 as a value will show as Empty and it is faster to check the length than the contents of a cell.
There are a few other minor changes.
What is it you are trying to do exactly.
Maybe we can suggest a nice tidy bit of code for you.
ATB
Steve.
check every cell in range, clarification...
Dear Steve,
Tanks a lot for your valuable comments.
i am just looking a little bit help and i am sure u can do it very easily. here i hve two buttons first for insert start_time and the another one is for end_tme i have a little bit problem related to the particular range.
when i am trying to check every cell in a range by for each loop then it is not going step by step i will give u the attached file and some example bellow...
Start_Time Button End_Tme Button
7:07:50 PM 7:07:50 PM
7:07:50 PM
7:07:50 PM
end time should not be there untill first time is not entered,i want to check every start time befor entring the end time by for loop and condition.
please feel free for more clarification.....
Regards
Nipendra
Hi there Nipendra, I am not
Hi there Nipendra,
I am not quite understanding the disired results of the code.
When you press StartTime you wish the next Blank Cell in the start column to have the time filled in?
When you press FinishTime you wish the next Blank Cell in the Finish Column to fill in, but only if there is a StartTime next to the Blank Cell?
For this to work you would not use a loop.
You would simply determine the last empty cell and fill it with the time if a condition is met.
Does this sound like what you require?
ATB
Steve.
apologise for tough writing
Ok Steve apologise for tough writing i am just going to elaborate what is happning here.....
Purpose - Employee Time Tracking
when i press the finish time button it should be check that first time is empty or not it means
u can not enter finish time untill u hav'nt enterd start time....
I think this is not quit enough understandable so now i have attached the real file which i am
developing so you can find out and i have inserted a comment over there.
Basically here i have a time tracker which is going to track the employee time in and time out,
i am using the same sheet to track the time adherence of particular employee,
here i want to bound everybody who is just going to fill his or her time schedule but could not be enter untill start time value in not there.
in the beginning i was thinking that with the help of for each loop i can check every cell in a particular range so loop will
check every cell and will show the message box it is not working like this,
this please may you suggest me what should i do. you can check attached sheet
Regards
Nipendra
Little more
I think I am getting lost on this line of your comment:
"when i press the finish time button it should be check that first time is empty or not it means u can not enter finish time untill u hav'nt enterd start time...."
So to understand the process.
1) Person presses "Start"
"Start": Start time is entered into the next empty cell down from the last time.
2) Person presses "Stop"
"Start": Start time is entered into the next empty cell down from the last time.
"Stop": Stop time is entered into the next empty cell down from the last Stop time. ONLY if a Start time exists?
There can be many "Started NOT Stopped lines".
There can be many "Started AND Stopped lines".
There can be no "Stopped lines only".
Or can there only ever be one line started and not stopped?
Would it be acceptable to have one button which does both start and stop actions? Press once to start, press again to stop?
I am certain that once we have ironed out the process the code will take no time at all.
Can veiw attached sheet
you can find attached file there,
i have mentioned what i required by comments.
We Have Two Time Here -:
1) Start Time
2) End Time
The user can insert End Time only if condition met(condition is Start Time value exist),
if condtion not met then should be show a message (please enter Start Time)
little bit code i have implemented more now -:
Sub End_Time()
Dim ws As Worksheet
Set ws = Sheet8
Dim rng As Range
'Set rng = Range("L5:L65536")
If Range("L5").Offset(0, -1).Value = "" Then
'Range("L5:L65536").End(xlUp).Offset(1, 0).Select
MsgBox ("Please enter the first time")
ws.Range("L65536").End(xlUp).Offset(1, 0).Select
If Selection.row = 2 Then
ws.Range("L5").FormulaR1C1 = Now()
ws.Range("L5").NumberFormat = "h:mm:ss AM/PM"
Else
Selection.FormulaR1C1 = Now()
Selection.NumberFormat = "h:mm:ss AM/PM"
End If
End If
End Sub
Regards
Nipendra
Are You There
Hi Steve,
R u there please give me a response???
Regards
Nipendra
Sample code
Hi there Nipendra, give the following code a go, see if it does what you need:
Sub End_Time()
Dim ws As Worksheet
Set ws = Sheet8
Dim lastrowK As Long, lastrowl As Long
lastrowl = ws.Cells(ws.Rows.Count, "L").End(xlUp).Row
If lastrowl = 1 Then
lastrowl = 5
Else
lastrowl = lastrowl + 1
End If
lastrowK = ws.Cells(ws.Rows.Count, "K").End(xlUp).Row
If lastrowl > lastrowK Then
MsgBox ("Please enter the first time")
Else
ws.Range("L" & lastrowl).FormulaR1C1 = Now()
ws.Range("L" & lastrowl).NumberFormat = "h:mm:ss AM/PM"
End If
End Sub
Thanks
Barman it is realy working.....
i am realy obliged that u help me out
thanks a lotttttttttttttttt
Nipendra