Find data on sheet using date

[johnsonk]

Hi, I have a sheet with data (Sheet1) and I have a search sheet (Sheet2) where I can search the data from sheet1 and shows the results in sheet2 at the moment I do a search entering a batch number and what I need is to be able to do another search by entering the date. Can anyone help..


Private Sub Search_Click()
Dim itemcode As Variant, i As Long
Dim R As Range, f As Range, cell As String

itemcode = Sheet2.Range("c2").Value 'search box
If itemcode = "" Then
MsgBox "Please Enter The Batch number!"
Exit Sub
End If

Set R = Sheet1.Range("A:H") 'column range
Set f = R.Find(itemcode, LookIn:=xlValues, Lookat:=xlWhole)
If Not f Is Nothing Then
cell = f.Address
i = 5 'start in 5th row
Do
Sheet2.Range("A" & i).Resize(1, 8).Value = f.Resize(1, 8).Value
i = i + 1
Set f = R.FindNext(f)
Loop While Not f Is Nothing And f.Address <> cell
MsgBox "Search Complete"
Else
MsgBox "Batch number does not exist!"
End If
End Sub

Private Sub ClearSearch_Click()
Range("C2").ClearContents
Range("A5:H1000000").ClearContents
ActiveWindow.ScrollRow = 1
Range("C2").Select
End Sub

Private Sub PrintSearch_Click()
Application.CommandBars.ExecuteMso ("PrintPreviewAndPrint")
End Sub

This is sheet2 so what I need is to search for batch number on certain date.

Regards

 

[DanteAmor]

Try this

I highlighted the modified rows

Private Sub Search_Click()
  Dim itemcode As Variant, i As Long
  Dim r As Range, f As Range, cell As String
  
  itemcode = Sheet2.Range("C2").Value 'search box
  If itemcode = "" Then
    MsgBox "Please Enter The Batch number!"
    Exit Sub
  End If
  
  Set r = Sheet1.Range("H:H") 'column range
  Set f = r.Find(itemcode, LookIn:=xlFormulas, Lookat:=xlWhole)
  If Not f Is Nothing Then
    cell = f.Address
    i = 5 'start in 5th row
    Do
      Sheet2.Range("A" & i).Resize(1, 8).Value = Sheet1.Cells(f.Row, "A").Resize(1, 8).Value
      i = i + 1
      Set f = r.FindNext(f)
    Loop While Not f Is Nothing And f.Address <> cell
    MsgBox "Search Complete"
  Else
    MsgBox "Batch number does not exist!"
  End If
End Sub

 

[johnsonk]

Hi, DanteAmor

I have tried the amended code and tried to do a search using both date and the batch with no joy.

Could I do it where they have to enter the batch in C2 and when they click in C4 they choose a date from a popup calendar then press search data is that possible ?

Regards

 

[DanteAmor]

You have to have a code to search by date and another to number.
In your images there is no data, you have to test with data.

 

[johnsonk]

There was data in sheet 1 the images are from sheet 2 after a search

 

[DanteAmor]

You can put the image of sheet1 to see the data.
What data do you have in column H on sheet1, are they dates or texts that looks like a date?
What do you put in cell C2 on sheet2 a date or a text that looks like a date?

 

[johnsonk]

Hi, DanteAmor
As you can see sheet 1 has the data and sheet 2 is where we do a search by searching the batch but what I also need is to also be able to search using the date.

SHEET 1 (Data)

SHEET 2 (Search)

Regards

 

[DanteAmor]

are they dates or texts that looks like a date?
What do you put in cell C2 on sheet2 a date or a text that looks like a date?

You could confirm that data.