HELLO ALL,
Quick question, maybe it's been covered already (I've searched but it does not cater for FIND & SELECT DATES
I need to create a macro that inserts a cell value in the FIND & SELECT dialogue box based on a cell value THAT IS DATE FORMATTED and find a matching cell on another worksheet that is a formula value
EG:-
cell sheet2!B11 =+Sheet2!B3 = 5 DEC 21....but changes as users input new dates
We need to find the value of B11...(5 DEC 21) in Sheet 1, which is a 2 years booking calendar for a small guest house
and move to this cell.
then move 2 cells down where we will then manually choose an available room and via a macro (CTR SHIFT R) input the reservation details
So far I have this code and it works for numbers but I can't get the right syntax for the cell value...but I think I'm trying to over-think the answer. In LOOKIN, i've tried xlValues AND xlFormulas BUT ONLY xlValues WORKS
Private Sub CommandButton1_Click()
'Go to the reservation date as set Cell B8
Dim FindString As String
Dim Rng As Range
FindString = Sheets("BOOKING REQUEST FORM").Range("B11").Value
Sheets("CALENDAR 21-22").Activate
If Trim(FindString) <> "" Then
With Sheets("CALENDAR 21-22").Range("A:BY")
Set Rng = .Find(What:=FindString, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "Nothing found"
End If
End With
End If
Sheets("CALENDAR 21-22").Activate
MsgBox "GO TO THE RESERVATION DATE " & Worksheets("BOOKING REQUEST FORM").Range("D10").Value & vbCrLf & "SELECT CELL, PRESS CTRL SHIFT R", , "ENTER RESERVATION ON THE CHART"
End Sub
I hope someone can help...it would be a great christmas present
Kind Regards and Seasons Greetings from South Africa
Bookmarks