4

Running a macro on an Excel spreadsheet, I get the Run-time error:

1004 Command could not be completed by using the range specified.

Selected Debug and the highlighted code is:

Selection.Subtotal GroupBy:=5, Function:=xlSum, TotalList:=Array(6, 7, 8, 9, _
    10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23), Replace:=True, _ 
    PageBreaks:= False, SummaryBelowData:=True

Looking elsewhere and I find this generally means a sort was attempted while there was not a selected cell.

However, the line immediately prior to the highlighted code is:

SubRange.Select

And the two lines immediately following these two are essentially identical, just sorting a different section of the sheet:

SortRange.Select
Selection.Sort Key1:=Range("E4"), Order1:=xlAscending, Key2:=Range("A4") _
    , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom

Is the difference tied up in the SortRange.Select versus the SubRange.Select?

In testing I've found the error occurs when a certain column containing numeric values is imported as text versus being imported as General. The column data is numeric, but it has leading zeroes and are not to be mathematically manipulated and so, by good Excel practices, ought to be treated as text.

Any ideas what I ought to do to try and diagnose this?

Improve this question
  • what range is selected? what is SubRange.Select referring to? – datatoo May 17 '11 at 22:35
  • @datatoo: That is a good question. I'm not sure. The only difference I've found between this script failing at this point and not failing is the datatype issue noted in the second to last paragraph of the initial question. – music2myear May 18 '11 at 14:54
  • can you step through and debug to see what the SubRange.Select is selecting? And are you sure each of the columns you are trying to run a subtotal on, are in fact numbers? – datatoo May 18 '11 at 15:18
  • Hmm, you bring up a good point. There are two or three columns where numeric characters are used but not as values (like account numbers, you don't really want to treat them as numbers). I'll check and see if for some reason the macro is trying to run a formula on these. – music2myear May 18 '11 at 15:24
  • it may even be that excel is not determining which are the headers. If the macro merely selects a cell in the range but the headers are not formatted differently, e.g. bold, underlined, etc. excel may not discern them. – datatoo May 19 '11 at 22:57
2

I would just add the following after Subrange.Select:

Selection.NumberFormat = "@"

to set the Subrange to the Text Format.

Improve this answer
  • Oooh, I'll have to remember that. Thank you. I'll see if it makes any difference. – music2myear May 23 '11 at 14:32
  • I haven't had time to implement this change due to other projects that are higher priority. I'll select this as the answer and open a new question or revisit this one if the issue persists. – music2myear Jun 20 '11 at 19:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.