Search between two dates and copy columns

[bowdown]

Hello everyone,

I would like some help on this task.

I need a script which searches between two dates in a column and then copies the entire corresponding columns between these two dates and pastes them to another sheet.


Please, pay attention that the date is in the format: d.m.yyyy hh:mm

Here is a sample:
sddssd.png


Link to crosspost:
http://www.mrexcel.com/forum/excel-q...y-columns.html

UPDATE. I am sorry I didnt know about that rule for another forum. My apologies.

[Olly]

Do you mean copy and paste the matching ROWS?

Try something like:

Please Login or Register  to view this content.

You may need to tweak this depending on how you are obtaining your date criteria, and where you want to paste the output....

[bowdown]

Thanks Olly, is there anyway I can enter the dates withing the sheet not in the macro? Yes, I mean copy and paste the matching rows.

[Olly]

Thanks Olly, is there anyway I can enter the dates withing the sheet not in the macro?

Yes, of course. Tweak this section:

Please Login or Register  to view this content.

to something like:

Please Login or Register  to view this content.

That's just an example, where your start date is in Sheet1 range F1, and your end date in Sheet1 range G1. You get the idea.

[bowdown]

Where should I paste the data with the columns and dates if start date is in F1 and end date in G1

[Olly]

I don't understand your question.

It might be easier to help you get the code precisely right, if you were to attach a sample workbook, which shows your source and target data structures - that is, where is the data coming from, where are the date criteria, and where do you want the matching rows pasted?

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[oeldere]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this http://www.excelguru.ca/content.php?184 to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.



http://www.mrexcel.com/forum/excel-q...y-columns.html

[bowdown]

dddd.png

Here is what I mean:

If you have dates in column E which are between the dates in B and C, COPY the dates together with the price from column E and F and paste them into column H and I for the first Open-Close pair, J and K for the second Open-close pair and so on.

I am really grateful that you respond and trying to help.


Cheers

[Olly]

Did you read oeldere's post?

Attach a workbook, not an image.

[bowdown]

I am sorry i didn't read it properly. Here is the file.

So if the date in 'F' column is between the date in 'A' and 'B', I need to copy the F date + the price ('G' column) in lets say 'J' and 'K' (that is for the first Open-Close and so on). Then I need to pick the second pair Open-Close and do the same check and copy in lets say M and N.

[oeldere]

Please Login or Register  to view this content.

1) To who you are replying?

2) did you read #7?

[bowdown]

Please Login or Register  to view this content.

1) To who you are replying?

2) did you read #7?

1) I am replying to #6, Olly.
2) Yes I did and I posted a link of the crosspost and I apologize I didnt know about that rule.

[Olly]

You could try something like this:

Please Login or Register  to view this content.

Be careful, though - you are VERY nearly running out of columns, with this dataset. I'm not sure what you're actually doing with the output, but I'd suggest a different structure of output, to prevent errors caused by trying to use too many columns, if your dataset increases.

[bowdown]

Thanks Olly, I tested it, it works great. I really appreciate your work.

[Olly]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. You can also 'Add Reputation' to users who have helped you. Thanks.

[bowdown]

Hi Olly,

Thanks again for the macro. However, I tried it on a different PC and it doesnt work. I mean it runs out, no errors but nothing happens.

Also, when I click 'Step Into', it highlights the first sub() line.

I tried different macro code as msgbox "hello" and it worked.

Macros are enabled. My excel version is 2013.


Any thoughts?

[Olly]

The code handles errors by writing the error message to the immediates window. So you could look at the immediates pane (press Ctrl G in the VBE) to see what error is happening.

To debug more fully , comment out the following line:

Please Login or Register  to view this content.

which will cause the procedure to break on an error. Let me know what happens then - which error type is being thrown, on which line?

[bowdown]

The code handles errors by writing the error message to the immediates window. So you could look at the immediates pane (press Ctrl G in the VBE) to see what error is happening.

To debug more fully , comment out the following line:

Please Login or Register  to view this content.

which will cause the procedure to break on an error. Let me know what happens then - which error type is being thrown, on which line?

Immediate window is empty. No error shown on any line.

[Olly]

Have you commented out the "On Error Goto Terminate" line, then run the procedure? And there is NO error?

Please attach the workbook you are trying to use.

[bowdown]

Have you commented out the "On Error Goto Terminate" line, then run the procedure? And there is NO error?

Please attach the workbook you are trying to use.

Yes, I tried with the comment out. Here is the file.

[Olly]

Okay, well that attachment works perfectly for me...