Search for a specific date in a column

[j844929]

Hi,

Can anyone tell me the code for looking for a particular date in a column and copying that row of data, for pasting into another document?

Hope you can help,

Tim

 

[Mudface]

Something like this should work OK, change the location to paste and the date searched for to suit-

Sub FindAndPaste()

Cells.Find(DateValue("3/1/03")).EntireRow.Copy Workbooks("Book3").Worksheets(1).Range("A1")
    
End Sub

 

[j844929]

It's a start, but the search will basically be a different date each time it is carried out...

Ideally, what I'd really like to do is to search between two dates (entered into a userform) and copy all rows between these two dates.

Do you know how to do this?

 

[Mudface]

Here's one way, assigned to a command button. You'll need to include error-handling etc and change the names of the textboxes if necessary to match your own-

Private Sub CommandButton1_Click()
Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Cells.Find(DateValue(TextBox1)).EntireRow
Set rng2 = Cells.Find(DateValue(TextBox2)).EntireRow

Set rng1 = rng1.Resize(rng2.Row + 1 - rng1.Row)
rng1.Copy Workbooks("Book5").Worksheets(1).Range("A1")

End Sub

 

[j844929]

Does the information have to be set up in certain columns? The code stops runnning at the following line:

Set rng1 = rng1.Resize(rng2.Row + 1 - rng1.Row)

My dates are in column K

 

[Mudface]

How do you mean, stops running? With an error message?

Does this one-liner variation work?

Private Sub CommandButton1_Click()

Range(Cells.Find(DateValue(TextBox1)).EntireRow, _
Cells.Find(DateValue(TextBox2)).EntireRow).Copy Workbooks("Book5").Worksheets(1).Range("A1")

End Sub

 

[j844929]

No, doesn't work at all.

I've got the following piece of code which might work, but I can't figure out why the last line doesn't work... could you have a look at it and see if there are any possibilities there...?

Private Sub CommandButton1_Click()
Dim mystartdate As Date
Dim myenddate As Date
mystartdate = InputBox("Start date")
myenddate = InputBox("End date")
Range("G1").Value = mystartdate
Range("H1").Value = myenddate

With Range("G2:G" & Range("A65536").End(xlUp).Row)
.Formula = "=IF(AND(RC[-3]>=R1C7,RC[-3]<=R1C8),TRUE,"""")"
.Value = .Value
.SpecialCells(xlCellTypeConstants, 23).EntireRow.Copy
End With

End Sub

 

[Mudface]

Sorry, but both pieces of code I've given you work fine for me.

 

[j844929]

Can you show me a shot of the spreadsheet that you're used? It might be something in mine that is wrong...

 

[Mudface]

All I did was put a bunch of dates in random columns along with some made up data, then added a UserForm with 2 textboxes and a command button and included the code in the button's click event. I then added a second workbook to paste into. The code can fail if it can't find a particular date (hence the need for error-trapping) on the currently active sheet and the first piece of code can fail if the second date chosen is 'above' (in terms of row position) the first, which is why I posted up the second piece of code, which, so far, I can't get to not work.

You keep saying it 'doesn't work', what exactly do you mean? Does the code run, but nothing gets pasted, or does it stop with an error message?