Find cells within column

Hi,

I want to create a macro that can find a specific date within a column.

i have set up the follwing macro:

Sub Debut()

answer = MsgBox("Do you want to check for inspection?", vbYesNo)
If answer = vbNo Then Exit Sub
InputBox ("Enter your date (DD/MM/YY)")
With Worksheets(1).Range("a2:a9999")
Set c = .Find((myvalue), LookIn:=xlValues)
If Not c Is Nothing Then MsgBox ("No record found")
If yes Then Destination = ActiveCell

End With
End Sub


But this macros do not work . I would like:

*the rows with the same date a&s my input date to be highlighted

Are you able to help me or do you have any ideas how to do it? Because i am a bit

Thanks a lot

try this macro


Sub Macro8()
Dim init As Variant
Dim adr As Variant
Dim stor As Variant
init = 0
Columns("A:A").Select
answer = MsgBox("Do you want to check for inspection?", vbYesNo)
If answer = vbNo Then Exit Sub
myvalue = InputBox("Enter your date (DD/MM/YY)")
v = Split(myvalue, "/")
If Len(v(1)) = 1 Then
myvalue = v(0) & "/" & "0" & v(1) & "/" & v(2)
End If

While init = 1 Or ActiveCell.Address <> adr
Selection.Find(What:=myvalue, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
If init = 0 Then
adr = ActiveCell.Address
stor = stor & ActiveCell.Row & ":" & ActiveCell.Row
Else
stor = stor & "," & ActiveCell.Row & ":" & ActiveCell.Row
End If
init = init + 1
Wend
Range(stor).Select
End Sub

Thanks you very much i tried it it works very well.

Hi and thanks again.

This time on the same macro, i would like to get a dialogue box that appear if the date is not put into the correct format and another dialogue box if the input date has not been found.


I have tried to havd a if function but it does not work, have you got any idea please?

Thanks you very much

Private Sub CommandButton1_Click()
Dim init As Variant
Dim adr As Variant
Dim stor As Variant
init = 0
Columns("K:K").Select
answer = MsgBox("Do you want to check for inspection?", vbYesNo + 256 + vbQuestion)
If answer = vbNo Then Exit Sub
myvalue = InputBox("Enter your date (DD/MM/YY)")
v = Split(myvalue, "/")
If Len(v(1)) = 1 Then
myvalue = v(0) & "/" & "0" & v(1) & "/" & v(2)
If Len(v(1)) = 0 Then MsgBox ("Date format should be DD/MM/YY")
End If



While init = 1 Or ActiveCell.Address <> adr
Selection.Find(What:=myvalue, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True).Activate
If init = 0 Then
adr = ActiveCell.Address
stor = stor & ActiveCell.Row & ":" & ActiveCell.Row
Else
stor = stor & "," & ActiveCell.Row & ":" & ActiveCell.Row
End If
init = init + 1
Wend
Range(stor).Select
End Sub

I added function to validate the entered date format.



Sub Macro8()
On Error GoTo er:
Dim init As Variant
Dim adr As Variant
Dim stor As Variant
init = 0
Columns("A:A").Select
answer = MsgBox("Do you want to check for inspection?", vbYesNo)
If answer = vbNo Then
Exit Sub
Else
a:
myvalue = InputBox("Enter your date (DD/MM/YY)")
myvalue = check_date_format(myvalue)
If myvalue <> "" Then
While init = 1 Or ActiveCell.Address <> adr
Selection.Find(What:=myvalue, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
If init = 0 Then
adr = ActiveCell.Address
stor = stor & ActiveCell.Row & ":" & ActiveCell.Row
Else
stor = stor & "," & ActiveCell.Row & ":" & ActiveCell.Row
End If
init = init + 1
Wend
Else
MsgBox "Date is not in DD/MM/YY format"
GoTo a:
End If
End If
er:
If stor <> "" Then
Range(stor).Select
Else
MsgBox "Records not found"
End If

End Sub




Function check_date_format(t As Variant)
v = Split(t, "/")
If UBound(v) = 2 Then
If v(0) <= 12 And v(1) <= 31 And v(0) >= 1 And v(1) >= 1 Then
If Len(v(2)) = 2 Then
If Len(v(1) = 1) Then
check_date_format = v(0) + "/" + "0" + v(1) + "/" + v(2)
Else
check_date_format = v(0) + "/" + v(1) + "/" + v(2)
End If
Else
check_date_format = ""
End If
Else
check_date_format = ""
End If
Else
check_date_format = ""
End If
End Function

Thanks a lot for helping me.

I have tried your macro, but it always open the dialogue box "Record not found", even if the date is in the column.

I made a deeper thought about what I liked the macro to do. In fact it is an excel file with 24 columns, and basically the objective is to search for date & highlight the rows containing that date in the set up column. To summarise it should work like that:

Dialogue box that says “Would you like to check for inspection?” – “Yes” “No”

a. No: End
b. Yes: Input Box “Enter you date DD/MM/YY” - 4 options

i. Cancel: End
ii. OK with nothing in the field: Dialogue box “Input Date?” - 2 options - "Yes" or "No"

1. Yes: go back to b
2. No: End

iii. Ok with wrong date format: dialogue box “Set date as DD/MM/YY” – OK - and go back to b

iv. OK with correct date format: 2 options

1.Dialogue Box “No records founds” – OK – END

2.Highlight rows with corresponding date and dialogue box “X inspections have been found” – X = Number of rows with found date – On same dialogue box “Would you like to print them?” - 2 options

a. Yes: Print only the selected rows to fit on one landscape page
b. No: End but keep rows selected



Macro for column K

People entering date in cells of the column – if date not entered into the following format DD/MM/YY then dialogue box “Please enter the date with the following format DD/MM/YY”. Go back to cell

I don't know if it clearer. But thanks you very much till know for your help.