VBA error message on selection of range (Excel03)

Topic

New Reply

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(“C4:D17”).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

Reply | Quote

Replies

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!
range("B4:B17").FormulaR1C1 = "12:30:00 AM"
will do what you need.
HTH

Reply | Quote

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 :

Private Sub cmdResetTime_Click()
    Range("B4:B17").FormulaR1C1 = "12:30:00 AM"
    Range("C4:D17", "I4:J17").ClearContents
    Sheets("Sheet2").Range("A2:B15").Copy Destination:=Range("C4")
End Sub

Andrew C

Reply | Quote

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

Reply | Quote

Sorry about that Fay.

Try the following :

Private Sub cmdResetTime_Click()
    Range("B4:B17").FormulaR1C1 = "12:30:00 AM"
    Union(Range("C4:D17"), Range("I4:J17")).ClearContents
    Sheets("Sheet2").Range("A2:B15").Copy Destination:=Range("C4")
End Sub

or

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

Andrew C

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Hello Legare thank you for your help.

The range is C4:D17.

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 C4:D17.

Fay

Reply | Quote

(Edited by sdckapr on 31-Dec-05 07:25. Changed routine to allow for clearing the cells…)

You can add something like this to the sheet obect:

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("c4:D17")

  If Not Intersect(Target, Range("C4:D17")) 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

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

=ROUND((D4-C4+(D4<C4))*24*4,0)/4

and in F4:

=IF(ISBLANK(C4),0,ROUND(((D4-C4+(D4<C4))-(B4/24/60))*24*4,0)/4)

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

Steve

Steve

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote