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

 

[azizrasul]

If you mean the country codes on the Main sheet, No. These country codes are fixed.

 

[Norie]

Well something is happening to you named ranges and what I'm trying to suggest is that you use code to create them.

Dim LastRow As Long
 
LastRow = Worksheets("Main").Range("A" & Rows.Count).End(xlUp).Row
 
Worksheets("Main").Range("A2"& LastRow).Name = "CountryCodes"

Change columns/rows as needed.

 

[azizrasul]

Norie, is the last line of code right? Should there be a semi-colon?

 

[Norie]

Why not try adding one?

Perhaps another A as well.

PS Why are you doing this in Excel?

Couldn't you import the data to Access and set up a UNION query to normalize the data?

If you do need the results in Excel export the query.

 

[azizrasul]

I'm not using Access as it's not intalled on my machine.
I'll try looking at the code tomorrow. Thank you for your help today.

 

[azizrasul]

Changed last line to

Worksheets("Main").Range("A2:A" & LastRow).Name = "CountryCodes"

and this made my original code work. GREAT.