Method Range of object Global failed

  • I have written a lengthy macro which I run on 12 sheets and it works perfectly for all sheets except one. The sheets are identical in format, same number of columns, same column headers. The number of rows varies from one sheet to another. I cannot figure out why I get the error message on only one of the sheets. I have included an attachment with most of the sheets deleted but the macro included. If you run the macro with the Boys8 sheet active, it will run properly as it does on all the sheets that aren't in the attached file. If you run the macro with the Girls8 sheet active, you get the error message. I was hoping that someone might have some suggestions as to what might cause this to happen and what I could look for. Any suggestions would be of great help.

    Files

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Method Range of object Global failed


    You add a formula then add subtotals, then fill down, using this line of code:

    Code
    1. Range("f5:f" & bottomE).FillDown

    .. except, if row 5 is a subtotal row ( as it will be in the girls sheet ), then you are filling down a blank cell ... ruining your processing. Change the order of your processing slightly and all will be OK. Do you see what I mean?


    Actually looking at the logic of the code, you might need to duplicate a bit of VBA ... am uploading the revised workbook now.


    [ATTACH=CONFIG]40589[/ATTACH]

    Files

    Cheers, Glenn.


    Beauty is in the eye of the beer-holder.

  • Re: Method Range of object Global failed


    Thank you so much, Glenn. I see exactly what you mean. I will play around with it to see what I can do. If you have any suggestions in the meantime, that would be great!

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Re: Method Range of object Global failed


    I managed to find a solution by inserting the formula into F5 if it was empty. This is what that section of code now looks like. I couldn't have done it without your help in finding the problem in the first place. Many thanks.


    [VB]
    'Copies formula in F5 down in Column F eqal to #rows in Column E
    bottomE = Range("e" & Rows.count).End(xlUp).Row
    Range("F5").Select
    If Range("F5") = "" Then Range("F5").FormulaR1C1 = _
    "=IF(((AND((RC[-5]=""""),(RC[-4]=""""),(RC[-3]="""")))),"""",IF((AND(RC[-5]<>"""",RC[-4]=R[-1]C[-4])),R[-1]C+1,IF (RC[-5]="""",R[-1]C,1)))"
    Range("f5:f" & bottomE).FillDown
    [/VB]

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.