Results 1 to 15 of 15
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA error message on selection of range (Excel03)

    I am sticking my nose into VBA for Excel for the first time and I need some help. I recorded macros and then put them into VBA. I will show you my code then the questions below. The task at hand is to help managers figure time cards, we are still in the 19th century. I used an example off of the internet to set up the basics that works correctly. Now I want to be able to have several buttons that allows the manager to clear the information just finished, reset the lunch time, and repopulated the basic shift times. That way they only have to change the ending time in most cases.

    Private Sub cmdResetTime_Click()
    ' ResetLunchTime Macro
    ' Macro recorded 12/29/2005 by Fay Yocum
    ' Keyboard Shortcut: Ctrl+Shift+T
    ' Resets the lunch time break to 30 minutes

    Range("B4:B17").Select
    ActiveCell.FormulaR1C1 = "12:30:00 AM"
    Range("B5").Select
    ActiveCell.FormulaR1C1 = "12:30:00 AM"
    Range("B6").Select
    ActiveCell.FormulaR1C1 = "12:30:00 AM"
    Range("B7").Select
    ActiveCell.FormulaR1C1 = "12:30:00 AM"
    Range("B8").Select
    ActiveCell.FormulaR1C1 = "12:30:00 AM"
    Range("B9").Select
    ActiveCell.FormulaR1C1 = "12:30:00 AM"
    Range("B10").Select
    ActiveCell.FormulaR1C1 = "12:30:00 AM"
    Range("B11").Select
    ActiveCell.FormulaR1C1 = "12:30:00 AM"
    Range("B12").Select
    ActiveCell.FormulaR1C1 = "12:30:00 AM"
    Range("B13").Select
    ActiveCell.FormulaR1C1 = "12:30:00 AM"
    Range("B14").Select
    ActiveCell.FormulaR1C1 = "12:30:00 AM"
    Range("B15").Select
    ActiveCell.FormulaR1C1 = "12:30:00 AM"
    Range("B16").Select
    ActiveCell.FormulaR1C1 = "12:30:00 AM"
    Range("B17").Select
    ActiveCell.FormulaR1C1 = "12:30:00 AM"

    ' ClearFields Macro
    ' Macro recorded 12/29/2005 by Fay Yocum
    '
    ' Keyboard Shortcut: Ctrl+Shift+C
    '
    Range("C417").Select
    Selection.ClearContents
    Range("I4:J17").Select
    Selection.ClearContents
    Sheets("Sheet2").Select
    Range("A2:B15").Select
    Selection.Copy
    Sheets("Sheet1").Select
    Range("C4").Select
    ActiveSheet.Paste
    Range("C4").Select

    End Sub

    Question 1: There has to be a shorter way to reset the lunch time at the top?
    Question 2: I have the lunch column format set as time. If a person only takes a 10 minute lunch break the total work time figures correctly at 8.50. The regular hours works out as 8 and the overtime comes in at 0.33. I tried changing the format of the lunch column to general and that didn't work. Staff does not clock in or out for lunch. Essentially when the managers figure the time card they just delete the first 30 minutes. I am thinking I am just better off telling them to enter the start of the shift 30 minutes later. What do you think?
    Question 3: When i run the ClearFields Macro section as a Macro it works like I want it to work. But with the code attached to a command button it hangs on the Range("A2:B15").Select line and the error message is Select method of Range class failed. Why?

    I have attached the file. Any and all help and instruction would be appreciated.
    Thank you. Fay

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VBA error message on selection of range (Excel03)

    I'm just on my way into a meeting so don't have time to look at the workbook now, but the answer to 1 is yes!
    <code>range("B4:B17").FormulaR1C1 = "12:30:00 AM"</code>
    will do what you need.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA error message on selection of range (Excel03)

    Rory has covered Question 1.

    Question 3 :-
    You have your code placed in a sheet module (Sheet1), so when the selection pertains to a different sheet (Sheet2), an error occurs. You really should place the code in a general module, and call if from the Button Click event.

    However, to incorporate Rory's suggestion and a solution to Q3, you code can be compressed to the following :<pre>Private Sub cmdResetTime_Click()
    Range("B4:B17").FormulaR1C1 = "12:30:00 AM"
    Range("C417", "I4:J17").ClearContents
    Sheets("Sheet2").Range("A2:B15").Copy Destination:=Range("C4")
    End Sub</pre>


    Andrew C

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA error message on selection of range (Excel03)

    Thank you Andrew this almost worked. It cleared E4:H17, which your code clearly did not include.

    Edited this I found the problem it is the two quotes between the ranges in the second line.

    Thank you so much. Fay

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA error message on selection of range (Excel03)

    Sorry about that Fay.

    Try the following :<pre>Private Sub cmdResetTime_Click()
    Range("B4:B17").FormulaR1C1 = "12:30:00 AM"
    Union(Range("C417"), Range("I4:J17")).ClearContents
    Sheets("Sheet2").Range("A2:B15").Copy Destination:=Range("C4")
    End Sub</pre>

    or<pre>Private Sub cmdResetTime_Click()
    Range("B4:B17").FormulaR1C1 = "12:30:00 AM"
    Range("C417").ClearContents
    Range("I4:J17").ClearContents
    Sheets("Sheet2").Range("A2:B15").Copy Destination:=Range("C4")
    End Sub</pre>

    Andrew C

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA error message on selection of range (Excel03)

    No I mean I got it working by taking out the two quotes, the ones on either side of the comma. Thanks you nailed it otherwise. Fay

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VBA error message on selection of range (Excel03)

    What are you looking for in number 2?

    If you want people to enter the number of minutes of lunch (10, 30, etc, format as general) and then you could use the formula (in F4):
    =IF(ISBLANK(C4),0,((D4-C4+(D4<C4))-(B4/24/60))*24)
    [The" /24/60" converts the "minutes" you enter into "days" which is the unit for "excel time"]

    If you want to assume a 30 min Lunch every day, you could just use:
    =IF(ISBLANK(C4),0,(D4-C4+(D4<C4))-1/48)*24)
    Or
    =IF(ISBLANK(C4),0,(D4-C4+(D4<C4))*24-1/2)

    If you are looking for something else can you elaborate?
    Steve

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA error message on selection of range (Excel03)

    Thank you Steve your first suggestion is exactly what I was looking for.

    Now I have a new question. How difficult is it to force to alter a manual entry?

    Here is what I am wondering is possible.

    If it is within the first 8 minutes of the quarter hour resent the entry to the previous quarter hour.

    For example if the manager enters 19:20 I would like Excel to resent the entry to 19:15. If 19:05 resent to 19:00. Then if they enter a time in the last 7 minutes of the next quarter hour mark, i.e. 19:25 gets reset to 19:30.

    Thank you. I am once again in over my head with VBA in Excel so I really appreciate the help you all have given me.

    Fay

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA error message on selection of range (Excel03)

    It would not be extremely difficult, you can use the Worksheet Change event routine. However, to give you the code we would need to know what cell or cells you want this to happen in.

    One other thing that you should know before we go any further. Since using the Worksheet Change event routine clears the Undo stack, you will no longer be able to use Undo on the worksheet. If you still want to do this, just tell us what cells.
    Legare Coleman

  10. #10
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA error message on selection of range (Excel03)

    Hello Legare thank you for your help.

    The range is C417.

    What I want to make sure is that the managers are only dealing with quarter hours in the E4:H17 range. I don't want them freaking out when they see 8.17 or 8.33 in the E4:H17 range. This can also happen with a change in the Lunch colum B4:B17 and the combination of events in the B4:B17 and the C417.

    Fay

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: VBA error message on selection of range (Excel03)

    <P ID="edit" class=small>(Edited by sdckapr on 31-Dec-05 07:25. Changed routine to allow for clearing the cells...)</P>You can add something like this to the sheet obect:

    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rCheck As Range
    Dim rCell As Range
    Dim dTimeConvert As Double
    On Error GoTo ErrRoutine
    Set rCheck = Range("c417")

    If Not Intersect(Target, Range("C417")) Is Nothing Then
    dTimeConvert = 15 / 60 / 24 '15 mins to days
    Application.EnableEvents = False
    For Each rCell In Target
    If Not IsEmpty(rCell) Then
    rCell.Value = Int(rCell.Value / dTimeConvert + 0.5) _
    * dTimeConvert
    End If
    Next
    End If

    ErrRoutine:
    Set rCheck = Nothing
    Set rCell = Nothing
    Application.EnableEvents = True
    End Sub</pre>



    Another option is if your only intent is not to have them "freaking out" based on the results in col e is to round those values. letting them enter whatever they want in the other columns with a formula like this in E4
    <pre>=ROUND((D4-C4+(D4<C4))*24*4,0)/4</pre>


    and in F4:
    <pre>=IF(ISBLANK(C4),0,ROUND(((D4-C4+(D4<C4))-(B4/24/60))*24*4,0)/4)</pre>


    Now E/F will be rounded to the quarter hours no matter what is entered in B, C or D ...

    Steve

    Steve

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA error message on selection of range (Excel03)

    I see that Steve has already answered your question. I will just add one thing. The routine that Steve posted needs to be put into the module that is behind the worksheet where you want it to act. To put it there do the following:

    1- Select the worksheet and right click on its tab.

    2- In the pop-up menu select "View Code".

    3- Paste Steve's code in the module that displays.
    Legare Coleman

  13. #13
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA error message on selection of range (Excel03)

    Thank you Steve and Legare. The first part worked exactly like it should. Thank you.

    I plugged in the Rounding formulas in and ran into problems. With the formula for E I got and error message that hung up on the "lt" part. I get the #NAME? display . When I paste the formula in for the F column I immediately get a #Value! error message and get the same "The formula you typed contains an error." message.

    Thank you. I will see if I can figure it out.

    Fay

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA error message on selection of range (Excel03)

    Did you copy the formulas from Steve's message and paste them into the workbook, or did you type them in. I ask because I don't see any "lt" in either of Steve's formulas, and if I copy them from Steve's message and paste them into the workbook you posted, they both seem to work perfectly. I have attached the workbook where I pasted them.
    Legare Coleman

  15. #15
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA error message on selection of range (Excel03)

    Darn I did it again. I copied it to Notepad and took it to another computer and when I had a problem I looked at notepad not the posting.

    Sorry. Thanks it is working exactly like it should.

    Fay

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •