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]

I don't need them at all. The original T1 and T2 sheets come from an external file. As I am copying these sheets into my workbook, I only need access to the raw data. Hence all the ranges of T1 and T2 can be deleted.

 

[Norie]

Aziz

Well that's the answer - don't use them in your code.

You don't even really need to delete them, but it would probably be a good idea.

As to what to replace them with, we could probably help if you explained what ranges you are trying to reference.

 

[azizrasul]

But I need the ranges of the same name on the 'Main' sheet. When I deleted all the ranges in T1 and T4 and then ran the code, it still didn't work.

I'm now getting Type mismatch when I debug

Range("CountryNames").Value

 

[Norie]

Aziz

Can you please explain why you must have the named ranges that came with this workbook?

They don't seem to be working very well.

If you do need named ranges then if you can explain what they are supposed to refer to we can possibly help you create/recreate the ones that aren't working.

 

[azizrasul]

On the Main sheet in MY workbook, I have a list of 22 country codes e.g.

GBR
IRL
DNK

in a column. The range is called CountryCodes.

I then obtain data from a spreadsheet (T1 and T4). On the T4 sheet, I have a row with the country codes with data underneath each code. The row upon which the country codes on T4 may change. Hence my original code essentially finds the row that contains the country codes on T4.

Having gone to all the trouble of writing the original code which did work for other data files I have changed the code to

    For iCol = 1 To Sheets("T4").UsedRange.Columns.Count
        If intCountryLine > 0 Then
            Exit For
        End If
        For x = 1 To Sheets("T4").UsedRange.Rows.Count
            If Cells(x, iCol) = "GBR" Then
                intCountryLine = x
                Exit For
            End If
        Next x
    Next iCol

which does the same job.

My frustration is that I can't see why the original code isn't working since I deleted the ranges from T1 and T4.

 

[Norie]

Aziz

Did you make a mistake when you created the ranges?

 

[azizrasul]

No I don't think so. Before I started this thread, it worked for data for Jan, Feb, Mar, Apr, May, Jul. It was the Jun data that caused the problem. When I then went back to doing the Jul again it stopped working. You can imagine how frustrating it all is.

The new code does the job so I guess I have to accept it. I always like to learn why something doesn't work so that I don't repeat the same mistake again.

I can post all the code, but it will be longish and I'm sure not very good code. Haha. I'm a MS Access person not a MS Excel person.

 

[Norie]

Aziz

So how did you create the named ranges?

 

[azizrasul]

Simply entered the codes on the Main sheet and selected them. I then went to the Name box and entered CountryCodes.

 

[Norie]

Are you importing this data from the external source you mentioned earlier?