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.
Method Range of object Global failed
-
-
-
Re: Method Range of object Global failed
You add a formula then add subtotals, then fill down, using this line of code:
.. 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]
-
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!
-
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] -
Re: Method Range of object Global failed
My pleasure.
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!