Solved

Excel VBA

Posted on 2014-07-22
8
266 Views
Last Modified: 2014-07-24
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
Comment
Question by:CABRLU63
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40213805
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
 
LVL 10

Expert Comment

by:FamousMortimer
ID: 40214099
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
 

Author Comment

by:CABRLU63
ID: 40214743
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
Save the day with this special offer from ATEN!

Save 30% on the CV211 using promo code EXPERTS30 now through April 30th. The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

 
LVL 10

Expert Comment

by:FamousMortimer
ID: 40214748
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
 

Author Comment

by:CABRLU63
ID: 40215340
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
 
LVL 10

Accepted Solution

by:
FamousMortimer earned 500 total points
ID: 40216547
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
 

Author Comment

by:CABRLU63
ID: 40216726
It works now, thanks for help!
0
 
LVL 10

Expert Comment

by:FamousMortimer
ID: 40216730
No problem.  Glad we got it worked out!
0

Featured Post

Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Today companies are subjected to more-and-more data, and it won't stop any time soon.  But there are obvious opportunities for reducing data, particularly data duplicated among companies.
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question