Thread: Spreadsheet - Method 'Range' of object '_global' failed

Hello. I'm new to this forum but after browsing it for a bit I feel confident that some of you probably will be able to help me with my problem.

I play the known MMO World of Warcraft and I use spreadsheets to try and maximize my performance. However I am having trouble with a specific spreadsheet. If you want to take a look at it to be able to picture it in your mind you can get the spreadsheet here.

The problem:
When pressing a button which should handle some data I get a runtime error 1004 "Method 'Range' of object '_global' failed".
I get this error when pressing 3 different buttons in the spreadsheet. The debug code is the same for 2 of the errors and I will list the debug below

Public Sub Socket_Correct()
'Correct Gems
 Dim CurCell As Object
 Dim blue As Integer
 Dim yellow As Integer
   
    blue = 0
    yellow = 0
  
    For Each CurCell In Range(Names("MyGems"))
        If CurCell.Offset(0, -1).Value = "m" Then
            CurCell.Value = "21 crit & 3% Increased Crit Dmg"
        ElseIf CurCell.Offset(0, -1).Value = "r" Or CurCell.Offset(0, -1).Value = "y" Or CurCell.Offset(0, -1).Value = "b" Then
            If Range(Names("GemCap")) = "Rare" Then
                CurCell.Value = "Bold Scarlet Ruby (16 Str)"
            ElseIf Range(Names("GemCap")) = "Epic" Then
                CurCell.Value = "Bold Cardinal Ruby (20 Str)"
            End If
        Else
            CurCell.Value = "None"
        End If
    Next
     For Each CurCell In Range(Names("MyGems2"))
        If CurCell.Offset(0, -1).Value = "m" Then
            CurCell.Value = "21 crit & 3% Increased Crit Dmg"
        ElseIf CurCell.Offset(0, -1).Value = "r" Or CurCell.Offset(0, -1).Value = "y" Or CurCell.Offset(0, -1).Value = "b" Then
            If Range(Names("GemCap")) = "Rare" Then
                CurCell.Value = "Bold Scarlet Ruby (16 Str)"
            ElseIf Range(Names("GemCap")) = "Epic" Then
                CurCell.Value = "Bold Cardinal Ruby (20 Str)"
            End If
        Else
            CurCell.Value = "None"
        End If
    Next
    For Each CurCell In Range(Names("MyProfession"))
        If CurCell.Offset(0, -1).Value = "Blacksmith" And (Range(Names("Profession1")).Value = "Blacksmithing" Or Range(Names("Profession2")).Value = "Blacksmithing") Then
            If Range(Names("GemCap")) = "Rare" Then
                CurCell.Value = "Bold Scarlet Ruby (16 Str)"
            ElseIf Range(Names("GemCap")) = "Epic" Then
                CurCell.Value = "Bold Cardinal Ruby (20 Str)"
            End If
        Else
            CurCell.Value = "None"
        End If
    Next
End Sub

Public Sub Compress()
    Range(Names("MyGems")).EntireRow.Hidden = True
    Range(Names("MyGems2")).EntireRow.Hidden = True
    Range(Names("MyBonus")).EntireRow.Hidden = True
    Range(Names("MyEnchants")).EntireRow.Hidden = True
    Range(Names("MyProfession")).EntireRow.Hidden = True
    
    For Each CurCell In Range(Names("MyProfession"))
        If Range(Names("Profession1")).Value <> "Blacksmithing" And Range(Names("Profession2")).Value <> "Blacksmithing" And CurCell.Offset(0, -1).Value = "Blacksmith" Then
            CurCell.Value = "None"
        End If
        If Range(Names("Profession1")).Value <> "Enchanting" And Range(Names("Profession2")).Value <> "Enchanting" And CurCell.Offset(0, -1).Value = "Enchant" Then
            CurCell.Value = "None"
        End If
        If Range(Names("Profession1")).Value <> "Leatherworking" And Range(Names("Profession2")).Value <> "Leatherworking" And CurCell.Offset(0, -1).Value = "LW" Then
            CurCell.Value = "None"
        End If
    Next
    
End Sub

Public Sub Expand()

Dim CurCell As Object
    
    Range(Names("MyEnchants")).EntireRow.Hidden = False
    
    For Each CurCell In Range(Names("MyGems"))
        If CurCell.Offset(0, -1).Value = "m" Then
            CurCell.EntireRow.Hidden = False
        ElseIf CurCell.Offset(0, -1).Value = "r" Then
            CurCell.EntireRow.Hidden = False
        ElseIf CurCell.Offset(0, -1).Value = "y" Then
            CurCell.EntireRow.Hidden = False
        ElseIf CurCell.Offset(0, -1).Value = "b" Then
            CurCell.EntireRow.Hidden = False
        ElseIf CurCell.Offset(0, -1).Value = "bo" Then
            CurCell.EntireRow.Hidden = False
        Else
            CurCell.EntireRow.Hidden = True
        End If
    Next
    
        For Each CurCell In Range(Names("MyGems2"))
        If CurCell.Offset(0, -1).Value = "m" Then
            CurCell.EntireRow.Hidden = False
        ElseIf CurCell.Offset(0, -1).Value = "r" Then
            CurCell.EntireRow.Hidden = False
        ElseIf CurCell.Offset(0, -1).Value = "y" Then
            CurCell.EntireRow.Hidden = False
        ElseIf CurCell.Offset(0, -1).Value = "b" Then
            CurCell.EntireRow.Hidden = False
        ElseIf CurCell.Offset(0, -1).Value = "bo" Then
            CurCell.EntireRow.Hidden = False
        Else
            CurCell.EntireRow.Hidden = True
        End If
    Next
    
    For Each CurCell In Range(Names("MyBonus"))
        If CurCell.Offset(0, -1).Value = "m" Then
            CurCell.EntireRow.Hidden = False
        ElseIf CurCell.Offset(0, -1).Value = "r" Then
            CurCell.EntireRow.Hidden = False
        ElseIf CurCell.Offset(0, -1).Value = "y" Then
            CurCell.EntireRow.Hidden = False
        ElseIf CurCell.Offset(0, -1).Value = "b" Then
            CurCell.EntireRow.Hidden = False
        ElseIf CurCell.Offset(0, -1).Value = "bo" Then
            CurCell.EntireRow.Hidden = False
        Else
            CurCell.EntireRow.Hidden = True
        End If
    Next
    
    For Each CurCell In Range(Names("MyProfession"))
        If Range(Names("Profession1")).Value = "Blacksmithing" And CurCell.Offset(0, -1).Value = "Blacksmith" Then
                CurCell.EntireRow.Hidden = False
        ElseIf Range(Names("Profession2")).Value = "Blacksmithing" And CurCell.Offset(0, -1).Value = "Blacksmith" Then
                CurCell.EntireRow.Hidden = False
        ElseIf Range(Names("Profession1")).Value = "Enchanting" And CurCell.Offset(0, -1).Value = "Enchant" Then
                CurCell.EntireRow.Hidden = False
        ElseIf Range(Names("Profession2")).Value = "Enchanting" And CurCell.Offset(0, -1).Value = "Enchant" Then
                CurCell.EntireRow.Hidden = False
        ElseIf Range(Names("Profession1")).Value = "Leatherworking" And CurCell.Offset(0, -1).Value = "LW" Then
                CurCell.EntireRow.Hidden = False
        ElseIf Range(Names("Profession2")).Value = "Leatherworking" And CurCell.Offset(0, -1).Value = "LW" Then
                CurCell.EntireRow.Hidden = False
        End If
    Next
    
End Sub

I have talked to the person maintaining the spreadsheet now but all she could tell me was to use Excel 2003 instead of 2007. I then tried using Excel 2003 but still got the error. Then I tried installing Office 2003 in a fresh copy of windows xp pro in a virtual machine to rule out anything I might have messed up myself but still got the error.

I would really like some help regarding this matter as I have no clue why I keep getting this error. Any help is appreciated.

Thank you.

hi,

For some reason I can't access the file, are you able to attach it to this thread in Excel 2003 format?
This error may suggest that the named ranges don't exist. Can you see either of them listed under Insert - Names - Define?

Range(Names("MyEnchants")).EntireRow.Hidden = False

Is the sheet protected?
Does the below work?

Range("MyEnchants").EntireRow.Hidden = False

btw, from my experience & a quick test (but only on the Activesheet) you don't need to use the syntax

range(names("Gemms"))
'you can just use
range("Gemms")

but... I do stand to be corrected!

hth
Rob

Thank you for replying

1. I have attached the spreadsheet to this post.
2. Yes I can see the range names and I know other people using Excel 2003 can use this spreadsheet without problems.
3. I don't think the spreadsheet is protected but how can I verify this?
4. How do I execute

Range("MyEnchants").EntireRow.Hidden = False

to check if it works?

I'm a noob to Excel still so thanks for your patience with me

Edit:

Originally Posted by broro183

hi,

Does the below work?

Range("MyEnchants").EntireRow.Hidden = False

btw, from my experience & a quick test (but only on the Activesheet) you don't need to use the syntax

range(names("Gemms"))
'you can just use
range("Gemms")

but... I do stand to be corrected!

hth
Rob

You sir, are a genious! I removed every (Names) part I could find in the debug code and now it works.
Thank you very much :D

Thanks for the feedback - I'm pleased I could help
If you're happy with the solution please mark the thread as solved.

re # 4:
To test (some) lines of code you can resize the VBE to let you see the Excel window as well, press [ctrl + G] in the VBE to bring up the immediate pane, type the the line of code to test & then press [enter].

edit: Ooopps!
I missed #3:
To check if a sheet is protected, activate the sheet in Excel & then look at Tools - Protection and it will either have the option of "Protect sheet" or "unprotect sheet".

hth
Rob