Move date by code entry.

cblincoln43

Board Regular
Joined
Mar 12, 2002
Messages
206
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!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top