[RESOLVED] VB 6.5 Data sort function

[KJDB1276]

Hi,

I'm new to VB and I'm trying to sort a region of data in Excel using the following code

Sheets(1).Range("A6").Select
ActiveCell.CurrentRegion.Select
Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, _
Key2:=Range("D2") , Order2:=xlAscending, Key3:=Range("B2"), _
Order3:=xlAscending, Header:= xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,_
DataOption2:=xlSortNormal, DataOption3:= xlSortNormal

but I keep getting an error message which says 'Identifier under cursor not recognised' and highlights the code in yellow, or which says 'The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank'. I am not that new to sorting data but I've been completely unable to solve this - if anyone has any idea about what this could be, I would greatly appreciate it.

Thanks,

Kerry

[vbfbryce]

What range is your data in?

When I try this, it works fine (my data is in a1:k6).

[jmcilhinney]

VB 6.5 is not VB.NET. I think it's VBA but I'm not 100% sure. The fact that you're working with Excel supports that theory. In that case, this thread should be in the VBA forum, where I have asked the mods to move this thread.

[si_the_geek]

Welcome to VBForums

Thread moved to the 'Office Development/VBA' forum... note that while it certainly isn't made clear, the "VB Editor" in Office programs is actually VBA rather than VB

VB 6.5 is not VB.NET. I think it's VBA but I'm not 100% sure.

You are correct... only VB 6.0 is VB6, all other 6.x are VBA

I have asked the mods to move this thread.

Thanks as always.

[KJDB1276]

My range is A6 to U30..I can see no reason why my code doesn't work! Perhaps there is a problem with the file. Thanks for your reply..

[si_the_geek]

I think I see the problem... on the .Sort line you are using the same unsafe coding style that Excel records macros in (eg: Range("H2") , etc), rather than the proper syntax as you've got on the first line of your code (which would make it: Sheets(1).Range("H2") , etc).

[vbfbryce]

When I copy your code verbatim into a macro, there is one thing that immediately causes an error. In your second to last line you are missing a space in front of the underscore at the end:

Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,_

When I add the space there, the macro works fine for me.

What version of Excel are you using?

[KJDB1276]

Hi,

Si_the_Geek thanks you have solved my problem - vbfbryce it is strange that some people seem to be able to use 'unsafe' code to sort data so that yours works and mine doesn't after you corrected my second error! But anyway it is resolved so thank you very much for your help.

Kerry

[si_the_geek]

I'm happy to help

it is strange that some people seem to be able to use 'unsafe' code to sort data so that yours works and mine doesn't

That is due to a bit of luck.

If you don't specify a parent object (in this case the Sheet), Excel will make a guess as to what you meant - and if you have a different file active (or are on the right file, but the wrong sheet) then it is highly likely to make the wrong guess, and therefore fail in some way (often an error, but sometimes by silently editing the wrong file).

In this particular case the error is because it ends up trying to sort the values based on columns that aren't even on the same sheet.

But anyway it is resolved so thank you very much for your help.

In that case could you please do us a little favour, and mark the thread as Resolved?
(this saves time reading for those of us who like to answer questions, and also helps those who search to find answers)

You can do it by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved". (like various other features of this site, you need JavaScript enabled in your browser for this to work).