CABRLU63
asked on
Excel VBA
Hello,
I have a sub with:
Dim lngRow As Long
Dim timeVal As Date
Dim HH, MM, SS As Integer
lngRow = 2
HH = Hour(Cells(lngRow, 7).Value)
MM = Minute(Cells(lngRow, 7).Value)
SS = Second(Cells(lngRow, 7).Value)
timeVal = TimeSerial(HH, MM, SS)
***If timeVal > Mid(Names("Shift1_Start"). Value, 2) And timeVal < Mid(Names("Shift1_End").Va lue, 2) Then
Cells(lngRow, 45).Value = 1
ElseIf timeVal > Mid(Names("Shift2_Start"). Value, 2) And timeVal < Mid(Names("Shift2_End").Va lue, 2) Then
Cells(lngRow, 45).Value = 2
ElseIf timeVal > Mid(Names("Shift3_Start"). Value, 2) And timeVal < Mid(Names("Shift3_End").Va lue, 2) Then
Cells(lngRow, 45).Value = 3
End If
getting this error:
"Run-time error '1004' application-defined or object-defined error" on the line *** (added *** for indication)
I do have Names defined under Name Manager in my sheet, six names
They look like this (one sample):
Name: Shift1_Start
Value: =0.6875 (this represents 7:45 AM)
Refers to: =0.6875
Scope= Workbook
Any help on resolution, appreciated!
Thanks!
I have a sub with:
Dim lngRow As Long
Dim timeVal As Date
Dim HH, MM, SS As Integer
lngRow = 2
HH = Hour(Cells(lngRow, 7).Value)
MM = Minute(Cells(lngRow, 7).Value)
SS = Second(Cells(lngRow, 7).Value)
timeVal = TimeSerial(HH, MM, SS)
***If timeVal > Mid(Names("Shift1_Start").
Cells(lngRow, 45).Value = 1
ElseIf timeVal > Mid(Names("Shift2_Start").
Cells(lngRow, 45).Value = 2
ElseIf timeVal > Mid(Names("Shift3_Start").
Cells(lngRow, 45).Value = 3
End If
getting this error:
"Run-time error '1004' application-defined or object-defined error" on the line *** (added *** for indication)
I do have Names defined under Name Manager in my sheet, six names
They look like this (one sample):
Name: Shift1_Start
Value: =0.6875 (this represents 7:45 AM)
Refers to: =0.6875
Scope= Workbook
Any help on resolution, appreciated!
Thanks!
Hi,
You can replace Names("... with
ThisWorkbook.Names(" and it should work.
A few things...
Let me know if you have any questions.
You can replace Names("... with
ThisWorkbook.Names(" and it should work.
If timeVal > Mid(ThisWorkbook.Names("Shift1_Start").Value, 2) And timeVal < Mid(ThisWorkbook.Names("Shift1_End").Value, 2) Then
Cells(lngRow, 45).Value = 1
ElseIf timeVal > Mid(ThisWorkbook.Names("Shift2_Start").Value, 2) And timeVal < Mid(ThisWorkbook.Names("Shift2_End").Value, 2) Then
Cells(lngRow, 45).Value = 2
ElseIf timeVal > Mid(ThisWorkbook.Names("Shift3_Start").Value, 2) And timeVal < Mid(ThisWorkbook.Names("Shift3_End").Value, 2) Then
Cells(lngRow, 45).Value = 3
End If
A few things...
This line Dim HH, MM, SS As Integer dimensions HH and MM as objects and SS as an integer so I imagine you wanted this: Dim HH As Integer, MM As Integer, SS As Integer
Why not timeVal = TimeValue(Cells(lngRow, 7).Value) instead of
Dim HH, MM, SS As Integer
lngRow = 2
HH = Hour(Cells(lngRow, 7).Value)
MM = Minute(Cells(lngRow, 7).Value)
SS = Second(Cells(lngRow, 7).Value)
timeVal = TimeSerial(HH, MM, SS)
?
Dim HH, MM, SS As Integer
lngRow = 2
HH = Hour(Cells(lngRow, 7).Value)
MM = Minute(Cells(lngRow, 7).Value)
SS = Second(Cells(lngRow, 7).Value)
timeVal = TimeSerial(HH, MM, SS)
?
Instead of using a defined name, you may just want to use variables in VBA unless you want a user to have the ability to changed the shift times, then you may want to use a name range rather than a defined name which will eliminate the need to use the MID function and clean up the code a bit. A named range differs from a defined name in that the RefersTo would refer to a range rather than a value.
Let me know if you have any questions.
ASKER
Thanks for tips.
One more issue though:
Only First Shift got populated in column 45.
Value of 1.
2 and 3 got not populated... Dang.
Any tips?
One more issue though:
Only First Shift got populated in column 45.
Value of 1.
2 and 3 got not populated... Dang.
Any tips?
You are welcome.
It would be hard to tell without seeing it. Any chance you can upload it?
I would start by checking the values of each cell/variable by stepping through the code line by line and try to see what it is (or isn't) doing properly.
It would be hard to tell without seeing it. Any chance you can upload it?
I would start by checking the values of each cell/variable by stepping through the code line by line and try to see what it is (or isn't) doing properly.
ASKER
OK, in debug mode my code runs and inputs "1" in column 45.
Then it skips rows of code. It only runs first 3 rows, the ones that input "1", the "2" and "3" are just skipped! Dang.
If timeVal > Mid(Workbooks(ConstWorkBoo kAppName). Names("Shi ft1_Start" ).Value, 2) And timeVal < Mid(Workbooks(ConstWorkBoo kAppName). Names("Shi ft1_End"). Value, 2) Then
Cells(lngRow, 45).Value = 1
ElseIf timeVal > Mid(Workbooks(ConstWorkBoo kAppName). Names("Shi ft2_Start" ).Value, 2) And timeVal < Mid (Workbooks(ConstWorkBookAp pName).Nam es("Shift2 _End").Val ue, 2) Then
Cells(lngRow, 45).Value = 2
ElseIf timeVal > Mid(Workbooks(ConstWorkBoo kAppName). Names("Shi ft3_Start" ).Value, 2) And timeVal < Mid(Workbooks(ConstWorkBoo kAppName). Names("Shi ft3_End"). Value, 2) Then
Cells(lngRow, 45).Value = 3
End If
Then it skips rows of code. It only runs first 3 rows, the ones that input "1", the "2" and "3" are just skipped! Dang.
If timeVal > Mid(Workbooks(ConstWorkBoo
Cells(lngRow, 45).Value = 1
ElseIf timeVal > Mid(Workbooks(ConstWorkBoo
Cells(lngRow, 45).Value = 2
ElseIf timeVal > Mid(Workbooks(ConstWorkBoo
Cells(lngRow, 45).Value = 3
End If
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It works now, thanks for help!
No problem. Glad we got it worked out!
pls try
If timeVal > Range("Shift1_Start").Valu
Use Range not Names
why use the mid function?
If you use < or > do not forget the equal or you will not catch all the data
Regards