PDA

View Full Version : Error while running Macro in excell



bjurcina
01-21-2010, 07:41 AM
Error message - item not found

Blue text is what is highlighted

I'm not a programer and I assume this may be simple to the folks on this forum. I can usually figure out what it's trying to do but in a case like this I need either a little push or dirrections to a source of information that will help me to better understand some of the basic commands.

I assume that it is saying to end the macro if mycoltosort = .Shapes(Application.Caller).TopLeftCell.Column and that it can't find some part of that line. ??

Thanks for any suggestions.





End If

myColToSort = .Shapes(Application.Caller).TopLeftCell.Column

Set myTable = .Range("A" & TopRow & ":A" _
& LastRow).Resize(, iCol)
If .Cells(FirstRow, myColToSort).Value _
< .Cells(LastRow, myColToSort).Value Then
mySortOrder = xlDescending

Bob Phillips
01-21-2010, 07:43 AM
Sounds like you have addressed it by its parent.

Show us all of the code.

bjurcina
01-21-2010, 02:37 PM
Thanks for the reply

This is it, including the author. There is another part but that is for setting up the invisible rectangles in the top row.


Sub SortTable()
'code written by Dave Peterson 2005-10-22
'2006-08-06 updated to accommodate hidden rows
Dim myTable As Range
Dim myColToSort As Long
Dim curWks As Worksheet
Dim mySortOrder As Long
Dim FirstRow As Long
Dim TopRow As Long
Dim LastRow As Long
Dim iCol As Integer
Dim strCol As String
Dim rng As Range
Dim rngF As Range
TopRow = 1
iCol = 10 'number of columns in the table
strCol = "A" ' column to check for last row
Set curWks = ActiveSheet
With curWks
LastRow = .Cells(.Rows.Count, strCol).End(xlUp).Row
If Not .AutoFilterMode Then
Set rng = .Range(.Cells(TopRow, strCol), _
.Cells(LastRow, strCol))
Else
Set rng = .AutoFilter.Range
End If

Set rngF = Nothing
On Error Resume Next
With rng
'visible cells in first column of range
Set rngF = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
End With
On Error GoTo 0

If rngF Is Nothing Then
MsgBox "No visible rows. Please try again."
Exit Sub
Else
FirstRow = rngF(1).Row
End If

myColToSort = .Shapes(Application.Caller).TopLeftCell.Column

Set myTable = .Range("A" & TopRow & ":A" _
& LastRow).Resize(, iCol)
If .Cells(FirstRow, myColToSort).Value _
< .Cells(LastRow, myColToSort).Value Then
mySortOrder = xlDescending
Else
mySortOrder = xlAscending
End If

myTable.Sort key1:=.Cells(FirstRow, myColToSort), _
order1:=mySortOrder, _
Header:=xlYes
End With
End Sub