Sorting by Passing a range object, help!

sll810

Board Regular
Joined
Jun 29, 2007
Messages
86
I am trying to call a sub procedure multiple times to sort a sheet by different parameters and copy particular fields into another sheet.

The subprocedure looks like the code below:

Code:
Sub SortCopyTopFirms(dsheet As Excel.Worksheet, ssheet As Excel.Worksheet, sortrange As Excel.Range, xlfcpy As Excel.Range, firmpaste As Excel.Range, valcopy As Excel.Range, valpaste As Excel.Range, sortkey As String)

Dim Lrow As Long
Dim Lcol As Integer
Dim Lcell As Excel.Range
Dim xlapp As Excel.Application
Dim sheetrange As Excel.Range


Lrow = dsheet.Range("A" & dsheet.Rows.Count).End(xlUp).Row
Lcol = dsheet.Cells(1, dsheet.Columns.Count).End(xlToLeft).Column
Set Lcell = dsheet.Cells(Lrow, Lcol)
Set sheetrange = dsheet.Range(dsheet.Range("A1"), Lcell)

sheetrange.Sort key1:=sortrange, Order1:=sortkey, header:=xlYes
xlfcpy.Copy
firmpaste.PasteSpecial Paste:=xlPasteValues
valcopy.Copy
valpaste.PasteSpecial Paste:=xlPasteValues

End sub

I am calling the procedure with the call
Call SortCopyTopFirms(datasheet, summarysheet, E5sortrange, firmcopy, IE5firmpaste, E5valcopy, IE5valpaste, dsortstring)

I keep getting the error message "Sort Method of Range Class failed"

I have defined all of the variables to the appropriate types and set them properly. I have never tried to sort by a range object, I usually state xsheet.range("A1"), etc. In this case I can't send the sheet reference since it will be called multiple times.

Does anyone have any quick fixes!?

Thanks!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi sll810,

I can't tell for sure that this is a problem since I don't know how you assigned E5sortrange in the calling procedure, but I recommend that you pass sortrange as a text string rather than a Range object. This is because the key1 variant parameter will accept either a text string cell address or a Range object. By doing it as a text string you avoid the potential problem of having the worksheet object part of the range object reference different from the worksheet being sorted (specified by sheetrange) which would cause a sort error. If passed as a string you could just pass "$A$1".

Damon
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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