VBA Runtime 438 error caused by 2003 version

M

molsonlabatt

New Member
Joined
Jul 25, 2012
Messages
14
Hi,

I have completed my code with 2010 and have have checked it on 2007 and both work great. When I try and use it on excel 2003 I get the runtime 438 error. The error occurs (in bold) on the Sort portion of my code, shown below.

Is there anyway I can modify the code to work with excel 2003

Thanks

Code: 
' Gets last row number and last column number
LastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
LastCol = ThisWorkbook.Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column

' Create name for all sort area
 Set SortA = ActiveSheet.Range(Cells(1, 1), Cells(LastRow, LastCol))

' Sort cells by last column
Range("A1").Select
 [B]ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear[/B]
 ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range(Worksheets("Sheet1").Cells(1, LastCol), Worksheets("Sheet1").Cells(LastRow, LastCol)), _
 SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
 With ActiveWorkbook.Worksheets("Sheet1").Sort
   .SetRange SortA
   .Header = xlGuess
   .MatchCase = False
   .Orientation = xlTopToBottom
   .SortMethod = xlPinYin
   .Apply
 End With
 
Sort by date Sort by votes
There is no Sort object in 2003, so you have to use the old Sort method instead - untested:

Code: 
' Gets last row number and last column number
LastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
LastCol = ThisWorkbook.Sheets("Sheet1").Cells(1, Columns.Count).End(xlToLeft).Column

' Create name for all sort area
 Set SortA = ActiveSheet.Range(Cells(1, 1), Cells(LastRow, LastCol))

' Sort cells by last column
SortA.Sort Key1:=SortA.Cells(1), Order:=xlAscending, Header:=xlGuess
 
Upvote 0
molsonlabatt,

I am currently using Excel 2007.

If I understand you correctly "Sort cells by last column".

Sample raw data:


Excel 2007
ABCDEFGHIJK
1111111111110
222222222229
333333333338
444444444447
555555555556
666666666665
777777777774
888888888883
999999999992
10101010101010101010101
11
Sheet1


After the macro:


Excel 2007
ABCDEFGHIJK
1101010101010101010101
299999999992
388888888883
477777777774
566666666665
655555555556
744444444447
833333333338
922222222229
10111111111110
11
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Rich (BB code): 
Option Explicit
Sub SortCellsByLastColumn()
' hiker95, 01/20/2014, ME751804
Dim SortA As Range
Dim LastRow As Long, LastCol As Long
Dim LastColName As String, h As String

' Sort cells by last column

With Sheets("Sheet1")
  LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
  LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
  Set SortA = .Range(Cells(1, 1), .Cells(LastRow, LastCol))
  LastColName = Replace(Cells(1, LastCol).Address(0, 0), 1, "")
  h = LastColName & 1
  SortA.Sort key1:=.Range(h), order1:=1
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the SortCellsByLastColumn macro.
 
Upvote 0
molsonlabatt,

I have used similar code in Excel 2007, and, Excel 2003.

You will have to test in all three versions of Excel that you have.
 
Upvote 0
hiker95 said:
molsonlabatt,

I am currently using Excel 2007.

If I understand you correctly "Sort cells by last column".

Sample raw data:

Excel 2007
ABCDEFGHIJK

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: #FFFF00, align: right"]10[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="bgcolor: #FFFF00, align: right"]9[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #FFFF00, align: right"]8[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="bgcolor: #FFFF00, align: right"]7[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="bgcolor: #FFFF00, align: right"]6[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="bgcolor: #FFFF00, align: right"]5[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="bgcolor: #FFFF00, align: right"]4[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="bgcolor: #FFFF00, align: right"]3[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="bgcolor: #FFFF00, align: right"]2[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="bgcolor: #FFFF00, align: right"]1[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



After the macro:

Excel 2007
ABCDEFGHIJK

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="bgcolor: #FFFF00, align: right"]1[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="bgcolor: #FFFF00, align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="bgcolor: #FFFF00, align: right"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="bgcolor: #FFFF00, align: right"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="bgcolor: #FFFF00, align: right"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="bgcolor: #FFFF00, align: right"]6[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="bgcolor: #FFFF00, align: right"]7[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #FFFF00, align: right"]8[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="bgcolor: #FFFF00, align: right"]9[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="bgcolor: #FFFF00, align: right"]10[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Rich (BB code): 
Option Explicit
Sub SortCellsByLastColumn()
' hiker95, 01/20/2014, ME751804
Dim SortA As Range
Dim LastRow As Long, LastCol As Long
Dim LastColName As String, h As String

' Sort cells by last column

With Sheets("Sheet1")
  LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
  LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
  Set SortA = .Range(Cells(1, 1), .Cells(LastRow, LastCol))
  LastColName = Replace(Cells(1, LastCol).Address(0, 0), 1, "")
  h = LastColName & 1
  SortA.Sort key1:=.Range(h), order1:=1
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the SortCellsByLastColumn macro.
Click to expand...

Thanks hiker95 it works just as I want.
cheers
 
Upvote 0
molsonlabatt,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
how to record a macro that includes "ctrl+shift+pagedown"
Replies
2
Views
99
jefals
J
T
  • Solved
VBA Compile Error: Named argument not found
Replies
4
Views
67
saboh12617
S
N
  • Solved
Optimize a color sorting recorded code
Replies
6
Views
76
Micron
M
N
  • Question Question
ActiveX Option Button to sort Data in another sheet and update conbobox
2 3
Replies
22
Views
593
NUC_N_FUTS2
N
B
  • Question Question
Excel VBA Object Variable or With Block Variable Not Set Error
2 3
Replies
28
Views
619
Alex Blakenburg
A

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top