Find expression causing "Subscript out of range" error

Hi everyone,

I've been trying for hours to get the following macro working, but it keeps giving me a "Subscript out of range" error. The error is at the Find expression, but I've been using the exact format I found from an example online, so I'm not sure what's going on.

As far as I can tell, it's not the sheet name, since I've tried it using the code name (used below) and the actual name (100% spelled correctly). I've also tried running it with different sheets and it's still the same issue. I also thought it might be related to the variables, but I've declared them according to what was in the example.

Please Login or Register  to view this content.

Just as a brief explanation of what I'm trying to achieve:

On the front page (Sheet2), the user selects a week number (e.g. "30") from a dropdown box in cell B5. They click an "Update" button which runs the macro.

The macro goes to a different worksheet (Sheet5 in this case) and searches for the week number as a string (e.g., "Week 30") in row 18 (from columns E to BD only). Note that Row 18 uses formulas to display the week numbers, which are called from a database. Then, once it finds the column for the appropriate week (e.g. column AB), it should copy cells E19 to E310 and paste it in that column in the same position (e.g. AB19 to AB310). Cells E19 to E310 contain formulas.

This process then needs to be completed across 4 other worksheets. The macro then ends.


This macro will be used on a weekly basis, so any help would be greatly appreciated!

Thanks,
aseret

Hi Holger,

Thanks, the code looks simpler and produces no errors. However, it also does not copy the formulas across

I'm not familiar with Match, but does it look for values, rather than formulas? The weeks are the result of formulas rather than just plain text.

I'm stumped once again! Any further clarification would be appreciated

Thanks,
aseret

Your code works, provide that this line is correct

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Holger, you are a genius! That amendment definitely works. I amended the columns to copy over towards.

Just two questions:
1. If I amend the range that is copied to F19:F310 or to G19:G310, etc. does the number I increase var by increase too? i.e. to 5, then 6, etc.
2. How do I go about giving you some sort of forum pat on the back?

AB33, thanks so much, at least I know I was on the right track! I'll probably abandon my original code because it wasn't as elegant.