PDA

View Full Version : Run-time error 1004 - on a sort?



wolf.stalker
11-16-2011, 11:28 AM
hi all, any help would be greatful..

Here is what you need to know without me posting everything up here.
Row 1 = just column headers
Column H = containes either "U", "P", "D", or "M"




iItemCount = Application.WorksheetFunction.CountIf(Sheets("Detail").Range("B:B"), Range("B" & 2))

Range("A" & 2, "Q" & iItemCount + 1).Select
ActiveWorkbook.Worksheets("Detail").Sort.SortFields.Add Key:=Range("H" & 2, "H" & 1 + iItemCount), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal





any idea why this might crash repeatedly on one particular row (around 183)? this is contained in a 1600 count loop. i have removed the "bad" rows but then it goes to the next row and crashes there....almost like the row is the issue, BUT, i am not sure how it CAN be as iItemCount has nothing to do with the row nunber?? iItemCount is just the number of times a site number is listed. :dunno

if you have any thoughts on how to fix or get around this, that would be awesome!

thank you!

wolf.stalker
11-16-2011, 12:30 PM
ok, another 30 mins looking at this and i still can't find the reason this is erroring out. my solution (not the best, but it works will be to use "On Error Resume Next"). I have run it with that in place and script runs perfectly with expected results.

if anyone has a better way around this, i am all ears!

Trebor76
11-16-2011, 08:46 PM
Hi there,

Unlike copying, sorting actually requires you to select the relevant tab and then sort it, i.e. assuming there is some data in the desired range, try this (untested):


With ActiveWorkbook.Worksheets("Detail")
.Select
iItemCount = Application.WorksheetFunction.CountIf(.Range("B:B"), Range("B" & 2))
.Range("A" & 2, "Q" & iItemCount + 1).Sort.SortFields.Add _
Key:=Range("H" & 2, "H" & 1 + iItemCount), _
SortOn:=xlSortOnValues, _
Order:=xlDescending, _
DataOption:=xlSortNormal
End With

HTH

Robert

mancubus
11-17-2011, 07:36 AM
try


Dim ws As Worksheet
Dim rng As Range
Set ws = Worksheets("Detail")
With ws
iItemCount = Application.CountIf(.Range("B:B"), .Range("B2"))
Set rng = .Range("A2:Q" & iItemCount + 1)
rng.Sort Key1:=.Range("H2"), Order1:=xlAscending, Header:=xlYes
End With