Use an Excel cell content to search a range on another sheet and return information

How do I use an Excel cell content (Date) to search a range on another sheet and return information about that date which is contained in the cells to the right of that date in the range. I need to check to see if a date is a Business Day and Not a Holiday, and if it is the End of the Month. The range has

Column 1     Column 2      Column 3    Column 4   Column 5
  Date     Day of Week     BD or WE    HD or NH    Y or N

BD = Business Day, WE = Weeken开发者_JS百科d, HD = Holiday, NH = Not Holiday, Y = End of Month, N = Not End of Month Every date from 2011 through 2016 is accounted for in the Range

I want to use the date entered by the user into a cell in a worksheet to search the range, find the date and use the information for that date to determine whether or not it is a good date. I am not new to programming, but I am about 20 years behind current programming languages so I am learning VBA code. I want to use VBA code because if the date entered is not a business day and not a holiday, then I want to bump the date up one and verify that date until I get a good date. I also want to bump the date down 1 if it is the end of the month. I believe I can enter the If statements and Looping myself, but I am having trouble knowing how to use the date entered by the user into a cell (rather than an Input Box) and returning the Information from the Range about that date so I can use it within a Loop until I can get the date I want. I appreciate any help you can give. Thanks

It would seem you want your Visual Basic code to trigger when a user enters information on your first sheet (let's call it the first sheet and by its codename; Sheet1). Therefore you need to write your sub-routine within Sheet1 in the Visual Basic Editor

Therefore you can use...

Private Sub Worksheet_Change(byval target as range)

Dim SelDate as Date

If VBA.IsDate(Cells(target.Row,target.Column)) Then
SelDate=Cells(target.Row, target.Column)

'Assume other range is on sheet with the codename Sheet2, starting at top of page in first column

LookRow = 1

     Do Until (Sheet2.Cells(LookRow, 1) >= SelDate and Sheet2.Cells(LookRow, 3) = "BD" and Sheet2.Cells(LookRow, 4) = "HD" and Sheet2.Cells(LookRow,5) = "N") or Sheet2.Cells(LookRow, 1) = ""
     LookRow = LookRow + 1
     Loop

     If Cells(LookRow, 1) <> "" Then
     Cells(target.Row, "OutputCol") = Sheet2.Cells(LookRow, 1)
     End If
End If

End Sub

This code actually bumps the date up one if it is the end of the month as your question has not specified what happens if bumping the date down results in a non-business day. You said you could cope with the IF statements so I'm not too concerned about that detail. You will need separate loops to find SelDate and the closest acceptable date if you are going to bump down dates as well as up

"OutputCol" is wherever you want to put the output on the initial sheet. In general I would recommend defining columns with reference to range names so as to make your code more robust. Expert help with that can be found here. I'd also use error handling

Note this only finds a single date at a time. If you are pasting a number of dates into the sheet, then it would only act on the top left cell of your range. "Target" as defined by the Worksheet_Change event will include the full selection so you could either re-process the full column when target.Count > 1 OR you can use target.Rows.Count to see how many rows to loop through