Method range of object global failed

[azizrasul]

I have the following code which I use with sheet T4 copied in from different monthly files. The code works fine except when I use T4 sheet for the June data it doesn't. The code simply tries to find which row the country codes begin. The country codes are

GBR
DEU

etc.

I get the error 1004 (Method range of object global failed if I comment out line On Error Resume Next.

    Sheets("T4").Select
    
    For iCol = 1 To ActiveSheet.UsedRange.Columns.Count
        If intCountryLine > 0 Then
            Exit For
        End If
        For x = 1 To ActiveSheet.UsedRange.Rows.Count
            strCountryCode = Cells(x, iCol)
            On Error Resume Next
            varResult = Application.WorksheetFunction.Match(strCountryCode, Range("CountryCodes"), 0)
            If Err = 0 Then
                intCountryLine = x
                y = iCol
                Exit For
            End If
            On Error GoTo 0
        Next x
    Next iCol

 

[Norie]

Aziz

Do you definitely have a named range called 'CountryCodes' in the workbook?

By the way, why are you using ActiveSheet?

If you want this code to work on 'T4' why not use Sheets("T4")?

 

[GlennUK]

... Norie is asking better questions that I was.

 

[azizrasul]

Point noted with regard to ActiveSheet. Changed the code for this.

Yes there is a range "CountryCodes". As I said in my opening post, it works for other files but not the June file? Somehow it doesn't recognise the range "CountryCodes". Prior to the code, if I enter

? Range("CountryNames").Rows.Count

in the debug window, I get an error (Application-defined or object-defined error).

 

[Norie]

Aziz

Which again suggests there is a problem with that named range.

Try this:

Sheets("T4").Range("CountryNames")

If that doesn't work post how this range has been defined and how you checked it's actually there.

 

[Ruddles]

Drop this into a new general code module and run it:-

[FONT=Fixedsys]Option Explicit[/FONT]
 
[FONT=Fixedsys]Public Sub DisplayRangeNames()[/FONT]
 
[FONT=Fixedsys]  Dim nm As Name[/FONT]
[FONT=Fixedsys]  Dim iPtr As Integer[/FONT]
[FONT=Fixedsys]  Dim sMessage As String[/FONT]
[FONT=Fixedsys] [/FONT]
[FONT=Fixedsys]  sMessage = ""[/FONT]
[FONT=Fixedsys]  For Each nm In ThisWorkbook.Names[/FONT]
[FONT=Fixedsys]    iPtr = InStr(nm.Name, "!")[/FONT]
[FONT=Fixedsys]    If iPtr > 0 Then[/FONT]
[FONT=Fixedsys]      sMessage = sMessage & Left(nm.Name, iPtr - 1) & " """ _[/FONT]
[FONT=Fixedsys]         & Mid(nm.Name, iPtr + 1) & """ (" _[/FONT]
[FONT=Fixedsys]         & Len(Mid(nm.Name, iPtr + 1)) & " characters)" & Space(10) & vbCrLf[/FONT]
[FONT=Fixedsys]    Else[/FONT]
[FONT=Fixedsys]      sMessage = sMessage & "Workbook """ & nm.Name & """ (" _[/FONT]
[FONT=Fixedsys]         & Len(nm.Name) & " characters)" & Space(10) & vbCrLf[/FONT]
[FONT=Fixedsys]    End If[/FONT]
[FONT=Fixedsys]  Next nm[/FONT]
[FONT=Fixedsys] [/FONT]
[FONT=Fixedsys]  MsgBox sMessage, vbOKOnly + vbInformation[/FONT]
[FONT=Fixedsys] [/FONT]
[FONT=Fixedsys] [/FONT]
[FONT=Fixedsys]End Sub[/FONT]

Named ranges should be displayed. If they have global scope, it will say "Workbook"; names local to a sheet will state the sheet name. The lengths of the names are also given in case there's an issue with unexpected characters.

Any surprises?

 

[azizrasul]

Ah, Ruddles there is a range in T4 in June's data of the same name, when running your code.

My CountryCodes range was in the "Main" sheet.

The ranges I get are

T1'!CountryCodes
T4'!CountryCodes
CountryCodes
T1'!CountryNames
T4'!CountryNames
CountryNames
T1'!IberianCodes
T4'!IberianCodes
IberianCodes
T1'!Major6
T4'!Major6
Major6
T1'!MonthValues
T4'!MonthValues
MonthValues
T1'!NordicCodes
T4'!NordicCodes
NordicCodes
T1'!OtherMarkets
T4'!OtherMarkets
OtherMarkets
T1'!Print_Titles
T4'!Print_Titles
T1'!TotalEurope
T4'!TotalEurope
TotalEurope
T1'!YearValues
T4'!YearValues
YearValues

If I use Norie's suggestion and change the code to

            varResult = Application.WorksheetFunction.Match(strCountryCode, Sheets("Main").Range("CountryNames"), 0)

it still doesn't work.

Is there a way of changing the following code

    ChDir strInputFolder
    Workbooks.Open Filename:=strInputFile
    Sheets("T1").Select
    Sheets("T1").Copy After:=Workbooks("Keysumm Generator.xlsm").Sheets(1)
    Windows(strInputFilename).Activate
    Sheets("T4").Select
    Sheets("T4").Copy After:=Workbooks("Keysumm Generator.xlsm").Sheets(1)
    Windows(strInputFilename).Activate
    ActiveWindow.Close

so that the T1 and T4 ranges are not included?

When I go to Insert|Name|Define (when in the T4 sheet) and select CountryCodes, it has

=#REF!#REF!

 

[Norie]

Eh, that doesn't look like what I suggest.

Anyway, it doesn't really matter - there's seems to be something, ie the #REF, wrong with the range anyway.

What ranges are these names meant to refer to?

How were they originally created?

Finally, do you actually need to use them?

Why not create/define your own?

 

[azizrasul]

Eh, that doesn't look like what I suggest.

Didn't quite understand that Norie.

I don't need the ranges in T1 or T4, I only need the data. The ranges that I have on the sheet "Main", I want to keep.

 

[Norie]

I suggested using Sheets("T4") not Sheets("Main").

That was before you mentioned the #REF errors but using Main wouldn't work either.

What exactly do these ranges refer to?

Do you need to keep them?