Search for a specific date in a column

j844929

Active Member
Joined
Aug 18, 2002
Messages
423
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
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
Something like this should work OK, change the location to paste and the date searched for to suit-

Code:
Sub FindAndPaste()

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

j844929

Active Member
Joined
Aug 18, 2002
Messages
423
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

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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-

Code:
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

Active Member
Joined
Aug 18, 2002
Messages
423

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
How do you mean, stops running? With an error message?

Does this one-liner variation work?

Code:
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

Active Member
Joined
Aug 18, 2002
Messages
423

ADVERTISEMENT

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
 

j844929

Active Member
Joined
Aug 18, 2002
Messages
423
Can you show me a shot of the spreadsheet that you're used? It might be something in mine that is wrong...
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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?
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,565
Messages
5,765,133
Members
425,263
Latest member
alcat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top