Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Moving date by using a code.

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    206
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Column D has Dates. Column H is for codes.
    Row 7 = 3/24-----------7="M","S","A","E",
    Row 8 = 3/25-----------8="H" or "W"
    Row 9 = 3/26-----------9=?
    Row 10 = 3/27---------10=?
    Row 11 = 3/28---------11=?
    Row 12 = 3/29---------12=?
    If the cells in column H has any of the above letters entered into them. Then the date for that row will transfer to the cell deginated for that letter. "M"=D18,"S"=D22,"A"=D25, "E"=D28,"H"=D31, and "W"=D34. Other wise these cells remain blank.

    The formulas Ya'll, helped me with work fine for one letter and one row. But when i try to add the rest of the letters and/or rows i keep getting #VALUE. Is this another one of them can't do deals? Like in inputing time as tenths of an hour.(2.5pm) There got be a way this outfit has 1500 employee's using a time clock that in 10ths.of a hour.

    [ This Message was edited by: cblincoln43 on 2002-04-28 11:34 ]

    [ This Message was edited by: cblincoln43 on 2002-04-28 11:43 ]

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You lost me with your "formulas Y'all" paragraph regarding tenths of an hour, but here is code to get you started with the first part of your post.

    Right click on your sheet tab, left click on View Code, and paste this in:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("H7:H12")) Is Nothing Then
    On Error GoTo ErrorHandler
    Select Case Target.Value
    Case Is = "M"
    [D18].Value = Target.Offset(0, -4).Value
    Case Is = "S"
    [D22].Value = Target.Offset(0, -4).Value
    Case Is = "A"
    [D25].Value = Target.Offset(0, -4).Value
    Case Is = "E"
    [D28].Value = Target.Offset(0, -4).Value
    Case Is = "H"
    [D31].Value = Target.Offset(0, -4).Value
    Case Is = "W"
    [D34].Value = Target.Offset(0, -4).Value
    Case Else
    MsgBox "In this cell, you can only enter" & vbCrLf & _
    "''M'', ''S'', ''A'', ''E'', ''H'', or ''W''," & vbCrLf & _
    "in upper case, without the quotes.", 16, _
    "A reminder..."
    Application.EnableEvents = False
    Target.Value = ""
    Application.EnableEvents = True
    End Select
    End If
    Exit Sub
    ErrorHandler:
    Target.Select
    Exit Sub
    End Sub

    The error trap is for when someone tries to delete all the values in the H7:H12 range.

    Notice that this code essentially validates the H7:H12 range for only the values you say you wanted, in upper case letters, with a message box to inform your users what happens if they try entering something different.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    206
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    A wile back i was looking for how excell brakes time down into tenths of an hour. So i could input it into a work sheet and I received a reply that it could not be done. This realy has nothing to do with this project. I just wanted to be sure i don't spend another two months if this can not be done. Sorry about the loosing you. now off to see if i can get this to work. because im not to bad at screewing things up the first time around time around. THANKS FOR THE HELP.

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,169
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Glad to help. I wonder what that tenth of an hour issue was, that someone said couldn't be done. One-tenth of an hour is 6 minutes and should be a manipulate-able enough number to work with. Repost as a separate question with some detail, if you are still interested in taking another shot at finding an answer.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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