Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

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").Value, 2) Then
      Cells(lngRow, 45).Value = 1
    ElseIf timeVal > Mid(Names("Shift2_Start").Value, 2) And timeVal < Mid(Names("Shift2_End").Value, 2) Then
      Cells(lngRow, 45).Value = 2
    ElseIf timeVal > Mid(Names("Shift3_Start").Value, 2) And timeVal < Mid(Names("Shift3_End").Value, 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!
0
CABRLU63
Asked:
CABRLU63
  • 4
  • 3
1 Solution
 
Rgonzo1971Commented:
Hi,

pls try

     If timeVal > Range("Shift1_Start").Value And timeVal < Range("Shift1_End").Value Then

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
0
 
FamousMortimerCommented:
Hi,

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

Open in new window


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)
?

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.
0
 
CABRLU63Author Commented:
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?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
FamousMortimerCommented:
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.
0
 
CABRLU63Author Commented:
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(ConstWorkBookAppName).Names("Shift1_Start").Value, 2) And timeVal < Mid(Workbooks(ConstWorkBookAppName).Names("Shift1_End").Value, 2) Then
      Cells(lngRow, 45).Value = 1
 ElseIf timeVal > Mid(Workbooks(ConstWorkBookAppName).Names("Shift2_Start").Value, 2) And timeVal < Mid (Workbooks(ConstWorkBookAppName).Names("Shift2_End").Value, 2) Then
      Cells(lngRow, 45).Value = 2
    ElseIf timeVal > Mid(Workbooks(ConstWorkBookAppName).Names("Shift3_Start").Value, 2) And timeVal < Mid(Workbooks(ConstWorkBookAppName).Names("Shift3_End").Value, 2) Then
      Cells(lngRow, 45).Value = 3
    End If
0
 
FamousMortimerCommented:
Ok I think I see what you mean.

You have all of the conditions in a single If statement.  You need to create an If for each comparison like so...

    If timeVal > Mid(ThisWorkbook.Names("Shift1_Start").Value, 2) And timeVal < Mid(ThisWorkbook.Names("Shift1_End").Value, 2) Then
        Cells(lngRow, 45).Value = 1
    End If
    
    If timeVal > Mid(ThisWorkbook.Names("Shift2_Start").Value, 2) And timeVal < Mid(ThisWorkbook.Names("Shift2_End").Value, 2) Then
        Cells(lngRow, 45).Value = 2
    End If
    
    If 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

Open in new window

0
 
CABRLU63Author Commented:
It works now, thanks for help!
0
 
FamousMortimerCommented:
No problem.  Glad we got it worked out!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now