The Range Object

[yumbelie]

I'm having some real trouble with the 'Range' object, and it's not the first time - basically , the debugger flags the request to select a cell or range of cells as failed, and I *cannot* see why. Code is as follows:

For x = 0 To UBound(sheetarray)
WeeklyReturnBook.Sheets("Sheet" & Trim(Str(x + 1))).Activate

Range("A1&quot.Select

Selection.Sort Key1:=Range("E2&quot, Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
WeeklyReturnBook.Sheets("Sheet" & Trim(Str(x + 1))).Name = sheetarray(x)
Next x

What happens is their is a workbook with 9 sheets (normally), each sheet is selected, and then I want to select a 'range' of cells (with the sort I believe it'll auto select all the data - so you need only select the top left hand cell - well, it's a direct copy from the macro ) - then sort them based on column E. Problem is, it's not working , and it's always the 'Range' object that causes trouble, as I understand it, it's syntatically correct - but, well, probablly something I'm missing

Cheers guys

Joseph.

 

[xlbo]

Have you stepped thru your code (using F8) to see what sheet is being activated ???
Doesn't seem to be anything particularly wrong with the code (except that I'd personally always define the range to be sorted explicitly) Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[yumbelie]

I have indeed stepped through the code and that is whats perplexing me, when you say define the sort range explicitly, do you mean as in Set SearchRange = "A1" ?

Thanks

Joseph.

 

[Nikita6003]

Nothing more infuriating than xl ranges ... but it could be Excel's not dealing with the worksheet/range objects properly - try explicitly declaring & setting your objects:

Dim l_wksCurrentSheet as Worksheet

...
  For x = 0 To UBound(sheetarray
    'Explicitly set the sheet you want to the object
    Set l_wksCurrentSheet =WeeklyReturnBook.Sheets("Sheet" & Trim(Str(x + 1)))

    'Now use the object to try to select your range
    l_wksCurrentSheet.Activate
    l_wksCurrentSheet.Range("A1").Select

  '... rest of code
  Next x
  'Release sheet object
  Set l_wksCurrentSheet = Nothing

'...

HTH
Cheers
Nikki

 

[RobBroekhuis]

Hey Nikki,
I've been wondering so long now, I just gotta ask - what does the l_ with which you preface all your variable names mean?
By the way, I think something else is going wrong in the post above, but we'll need more information about exactly WHAT is going wrong to be able to help.
Rob

 

[yumbelie]

Thanks Nikita, Worked perfectly after I forcefully pre-declared the range - Got some wierd quirks has old Excel :\