Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Move date by code entry.

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

    Default

    I have Column H, Rows 7 through 13 set up to manually enter codes by letter,(a,b,c,ect.)
    In column D, Rows 7 through 13, I have dates entered. When I enter a code into Column H, Rows 7 through 13, I want the date in that row to move to another area of the work sheet or into D17. (each letter will have it's own cell)
    In my last post the formula I received =if(h7="m",d7,"")worked well for one row of Column H, but not for all Rows, 7 through 13.
    I tried =if(h7:h13="m",d7:d13,"""") got #value....If 1 cell in h7 through h13="M" then Cell D17= the date in D7 through D13 that has the "M" in it........HEPL!!Im confused with this one!

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

    Default

    Here's a VBA solution that might work, if I follow 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)
    On Error GoTo ErrHandler
    If Not Intersect(Target, Range("H7:H13")) Is Nothing Then
    Application.ScreenUpdating = False
    Range("H7:H13").Select
    Selection.Find(What:="M", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
    True).Activate
    ActiveCell.Select
    [D17].Value = ActiveCell.Offset(, -4).Value
    Application.ScreenUpdating = True
    End If
    Exit Sub
    ErrHandler:
    Target.Select
    [D17] = ""
    Exit Sub
    End Sub

    The error handler is for when no "M" exists in the range H7:H13, in which case D17 will display nothing.

    Note this code is for a case sensitive, upper case "M".

    Also, this will return the first corresponding date in D17 that matches the first "M" in H7:H13, in case there is more than one "M" in that range.

    Any help?

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
  •