Moving date by using a code.

[cblincoln43]

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

 

[Tom Urtis]

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.

 

[cblincoln43]

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.

 

[Tom Urtis]

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.