Macro with ranges based on border format

[sxj]

This is an example of the sort of files I work on every week.

- It is NEVER guaranteed that certain keywords will be in the same column from project to project.
- But "Scenes Liked" and "Scenes Disliked" will ALWAYS be directly left to the regions in question.
- Similarly, the regions in question will ALWAYS be formatted with borders (nothing else in the data set will).

Border Example LD.JPG

I need to change the contents of the FIRST bordered region (D1:M1) from the unique text in each cell to "sc1" "sc2" "sc3" etc.
E.g. "Smith finds out he has a clone" CHANGES TO "sc1" / "Logos" CHANGES TO "sc2"

And I need to change the contents of the SECOND bordered region (P1:Y1) from the unique text in each cell to "scd1" "scd2" "scd3" etc.
E.g. "Smith finds out he has a clone" CHANGES TO "scd1" / "Logos" CHANGES TO "scd2"


This is part of the code I've been using, located in a SEPARATE workbook.
The macro finds "Scenes Liked," offsets by one, and sets the range as ActiveCell xlToRight.
It then finds the FIRST cell in that range that does NOT contain a formatted border and inserts a blank column before performing its other functions.

Please Login or Register  to view this content.

The same sort of code is then used for the SECOND region, "Scenes Disliked."



Whenever I try running the macro I receive this error message:

Capture.JPG


And the debugger highlights this section of code:

Please Login or Register  to view this content.

Any thoughts on how to fix this error or avoid it altogether with a different approach?
(Macro file attached, specific to Private Sub PivotDomestic section)

[Pepe Le Mokko]

Administrative note

If you are not using XL2003 any more,please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version.

Also in the future please post in the correct forum. I will move it for you
Thanks

[WideBoyDixon]

Can you provide a sample workbook which includes the formatted data as well? It's difficult trying to find a solution in the dark. My best guess for a simpler start to the macro would be:

Please Login or Register  to view this content.

WBD

[sxj]

Wow - this worked perfectly! It does exactly what I need it to and it's such a better approach. Thank you so much for your help!

[sxj]

Administrative note

If you are not using XL2003 any more,please update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to. Members tailor answers based on your Excel version.

Also in the future please post in the correct forum. I will move it for you
Thanks

Will do, thank you!