Dialogue box to search date range of table column, copy relevant rows to report sheet

Hi,

Could you please upload a workbook with this code in it so that I may see the code in context to have some chance of debugging it?

Or alternatively, tell me where your code is falling over (which line) and what error message is displayed.

Thanks

Please find mockup spreadsheet attached

Hi,

Your code appears to be breaking on the line

Please Login or Register  to view this content.

Now I haven't dealt with Excel tables much using VBA, but I'm not actually sure what the

Please Login or Register  to view this content.

part of this line is supposed to be achieving. If this is removed, then cell A1 is selected (is this what you were attempting to do???).

Another thing that may be bringing you unstuck is that Excel 2003 tables don't use the square brackets in VBA to the best of my knowledge, that is only since Excel 2007. Unfortunately I don't have Excel 2003 so I can't debug your code in the same environment

Let me know if either of these observations have helped

I am using MS Excel 2010. Regarding the break with the headers, this was caused because I change the name of one of the headers in the data sheet and forgot to update the formula (sorry). I have now fixed with the line - Range("Table2[[#Headers],[Committee]:[File path to documents]]").Select

The break causing the problem is the line - For Each cell In Range("B1:B500").Offset(A - 1, 0)

Ah sorry about that, it says you are using Excel 2003 on your user profile.

The problem with that line is that the Range.Offset property expects its RowOffset and ColumnOffset arguments to be positive, negative or zero. You are entering a RowOffset of "A - 1", which is a valid parameter if and only if "A" was defined as a variable or a constant with an integer-type value (which it is not in your code).

To allow me to propose to you a valid alternative, could you please explain what you were attempting to achieve with this line of code? Which range of cells were you trying to loop through?

Thanks

I want the macro to search dates that fall within a range (column b lists dates for each row), and for each date that falls in the range the corresponding row should be copied and added to the report. It is just that one line that has the bug and I think your right in that it probably needs to be defined earlier. Your help is appreciated.

No worries Tim, happy to help!

Please don't forget to mark this thread as solved and click on the * next to my post to say thanks

Have a great day!

Sorry, I don't think you uploaded the solution?? Thanks again in advance for solving it.

Whoops, no I didn't.

But before I can solve it anyway, I need you to tell me which row you are attempting to reference with the "A - 1"?

Thanks

All the rows in the Data Sheet.

OK then, try just removing the .Offset() altogether then, as this should then cycle through all cells from B1 to B5000. So

Please Login or Register  to view this content.

will become

Please Login or Register  to view this content.

Let me know

While no error message came up, not data was copied into report.

Try replacing this code

Please Login or Register  to view this content.

with this code

Please Login or Register  to view this content.

Let me know if this is copying what you want copied.

Sorry this has taken so many attempts, but it is hard to guess what it is that you want because it has not been made perfectly clear, and your code shows no real indication either (which is why it is not working).

Hope this helps

Thanks again airyan, my apologies for not being more concise. That seems to be working but any chance of copying the whole row (if relevant) across to the Report Sheet, rather than just the cell in column a?

No problems!

Sure, replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

and to copy all of the headers also, replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

Hope this is better

That's great news!

Thanks for posting the final solution also Tim.

Please don't forget to mark this thread as solved and click on the * next to my post to say thanks