I'm having trouble with a tiny function i wrote years ago, used to simply sort a specific column from A>Z of a list object.
It's randomly failed "Method range of object - 'Global' failed" But if i press F5 again when stuck, it continue normally (sometimes).
I don't understand how can i debug this, could you please help ?
For better understanding: gracode_Dico is a global dictionary used to get the correct value of the code with the lo name as imput.
The code fail at the line .SortFields.Add2 key:=Range(sRange), SortOn:= _
'Boucle sur All Listobjects si leur nom est t_, puis trie selon le code de la table.
Private Function GRA_Tri_By_Code(theworkbook As Workbook)
Dim sRange As String, ws As Worksheet, lo As ListObject
For Each ws In theworkbook.Worksheets
If Left(ws.Name, 2) = "t_" Then
For Each lo In ws.ListObjects
If Left(lo.Name, 2) = "t_" Then
Select Case lo.Name
Case "t_cable_patch201", "t_ltech_patch201", "t_zpbo_patch201", "t_cab_cond", "t_cond_chem", "t_love"
'Debug.Print "Impossible de trier " & lo.Name
Case Else
sRange = lo.Name & "[[#All],[" & gracode_Dico(lo.Name) & "_code]]"
'sRange = lo.Name & "[[#All],[" & "ba" & "_code]]"
With lo.Sort
.SortFields.Clear
.SortFields.Add2 key:=Range(sRange), SortOn:= _
xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Header = xlYes: .MatchCase = False: .Orientation = xlTopToBottom: .SortMethod = xlPinYin: .Apply
End With
End Select
End If
Next lo
End If
Next
End Function
I've tried to use ws.activate (not fond of it), no changes. I've tried to precise the parent of the range like ws.Range(sRange) or lo.Range(sRange) without success.
I don't understand how can I debug this. Add some error checking and/or diagnostic debug.print statments. For example
Option Explicit
Private Sub GRA_Tri_By_Code(theworkbook As Workbook)
Dim ws As Worksheet, lo As ListObject
Dim n As Long, sColumn As String
For Each ws In theworkbook.Worksheets
If Left(ws.Name, 2) = "t_" Then
For Each lo In ws.ListObjects
If Left(lo.Name, 2) = "t_" Then
Select Case lo.Name
Case "t_cable_patch201", "t_ltech_patch201", "t_zpbo_patch201", "t_cab_cond", "t_cond_chem", "t_love"
'Debug.Print "Impossible de trier " & lo.Name
Case Else
' check dictionary
If gracode_Dico.exists(lo.Name) Then
sColumn = gracode_Dico(lo.Name)
Else
MsgBox "No entry for " & lo.Name & " in gracode_Dico", vbCritical
Exit Sub
End If
' check column exists
Dim rngSort As Range
For n = 1 To lo.ListColumns.Count
'Debug.Print ws.Name, lo.Name, lo.ListColumns(n).Name, sColumn
If lo.ListColumns(n).Name = sColumn Then
Set rngSort = lo.ListColumns(n).Range
Exit For
End If
Next
' does not exist
If rngSort Is Nothing Then
MsgBox "Could not locate column '" & sColumn & _
"' in " & ws.Name & " " & lo.Name, vbCritical
Exit Sub
End If
With lo.Sort
.SortFields.Clear
.SortFields.Add2 Key:=rngSort, SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.Header = xlYes:
.MatchCase = False:
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Select
End If
Next lo
End If
Next
End Sub