Find cells within column

[Mickey71]

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

[anilsolipuram]

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

[Mickey71]

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

[Mickey71]

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

[anilsolipuram]

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

[Mickey71]

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.

[anilsolipuram]

minor change in the function, i thought it was mm/dd/yy format.

Now check if you finding any records. Once you get this resolve , you can surely expand your macro




Function check_date_format(t As Variant)
v = Split(t, "/")
If UBound(v) = 2 Then
If v(1) <= 12 And v(0) <= 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

[Mickey71]

thanks you very much you have helped me a lot.

I have made some modifications to meet the objective of the macro, but i have still 2 issues:

1. when you have to put your input date:If you press cancel the macro exit, but if you don't put any data and press ok, i'd like a message box to come and say: "Input Date?". Option no exit the macro and option yes go back to the input box. I can't make them work, eiter it is exit in both cases or msgbox in both cases.

2. I don't know how to count the number of rows that the macro find.

I have sorted out the print problem, because you can't print all the rows in one page, if they do not follow. That is a characteristics of excel. So I will just let it as it is. At the moment I have set up the print as a preview only, and I will change it later so as to not use a lot of paper.

The macros is now this one:



Sub CommandButton1_Click()
On Error GoTo er:
Dim init As Variant
Dim adr As Variant
Dim stor As Variant
init = 0
'Column K selection'
Columns("K:K").Select
'Message box to know if inspections want to be checked'
answer = MsgBox("Do you want to check for inspections?", vbYesNo + vbQuestion)
'Authorise to end the macro by pressing no'
If answer = vbNo Then
Exit Sub
'If yes is pressed then this programme work to search and highlight the searched inspections'
Else
a:
'Message box to enter the required date'
myvalue = InputBox("Enter your date (DD/MM/YY)")
'Authorise to exit macro with cancel'
If myvalue = "" Then Exit Sub

'Check the format of the date input'
myvalue = check_date_format(myvalue)
'if my value ok then begin search and find process'
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
'If date is not in the correct format then message box'
Else
MsgBox "Date is not in DD/MM/YY format", vbExclamation
'Go back to beginning of the search'
GoTo a:
End If
End If
'If no records found then message box'
er:
If stor <> "" Then
Range(stor).Select
'Message box for number of vehicle inspected found, and possibility to print them'
answer = MsgBox("X Vehicles have been found, would you like to print them?", vbYesNo + vbInformation)
'If answer is no then exit macro'
If answer = vbNo Then Exit Sub
'Otherwise print setup and print'
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.FitToPagesTall = 1
.FitToPagesWide = 1
End With
'Print on each page the title of each columns'
Worksheets("Sheet1").Activate
ActiveSheet.PageSetup.PrintTitleRows = ActiveSheet.Rows(3).Address
Selection.PrintPreview
'If no date found on the column, message box that says it'
Else
MsgBox "Records not found", vbCritical
End If

End Sub



'Function that check if the date is in the correct format DD/MM/YY'
Function check_date_format(t As Variant)
v = Split(t, "/")
If UBound(v) = 2 Then
If v(1) <= 12 And v(0) <= 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


If you have any ideas to help me to sort those two problems that i have it would excellent.

Thanks a lot