What should I do when this has no data?

[StargateFanFromWork]

Was hoping to get best approach to this situation below.

When the sheet has data in it, the macro below works perfectly:

*********************************************
Sub SortByDate()
'
'
Application.Goto Reference:="R1C2"
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("D2")
_
, Order2:=xlAscending, Key3:=Range("E2"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Range("B2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
End Sub
*********************************************

When there is no data, I obviously get an error code. The code says:


"Run-time error '1004':

Application-defined or object-defined error"

How can I have the sheet return a box saying, "There is no data to sort" or
something instead of that error code. Due to the great help here, I now
know how to write up a message box (yeay!) so that part is not so hard. But
how would I handle the other, pls?

Thanks!

 

[Tom Ogilvy]

Sub SortByDate()
'
'
Application.Goto Reference:="R1C2"
if ActiveCell.CurrentRegion.Count < 2 then
msgbox "No data to sort"
exit sub
end if
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, _
Key2:=Range("D2"), _
Order2:=xlAscending, _
Key3:=Range("E2"), _
Order3:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Range("B2").End(xlDown)(2).Select
End Sub

 

[StargateFanFromWork]

Sub SortByDate()
'
'
Application.Goto Reference:="R1C2"
if ActiveCell.CurrentRegion.Count < 2 then
msgbox "No data to sort"
exit sub
end if
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, _
Key2:=Range("D2"), _
Order2:=xlAscending, _
Key3:=Range("E2"), _
Order3:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Range("B2").End(xlDown)(2).Select
End Sub

Hi!

It's neat the way you changed the coding to do the same thing as the
recorded code. Cool. It didn't work though, sorry to say. I get the
identical error message when used on a blank sheet. When I put some dates
in, it still takes me to the empty cell below them after sorting, so that's
pretty neat. But the original problem doesn't seem to be dealt with.

I'm using XL2K on W2K, if that makes any difference? Sorry, I should have
said.

Thanks.

 

[Tom Ogilvy]

Well, I can only guess what your worksheet looks like and where the error
occurs, but try this

Sub SortByDate()
'
'
Application.Goto Reference:="R1C2"
Selection.Sort Key1:=Range("B2"), _
Order1:=xlAscending, Key2:=Range("D2"), _
Order2:=xlAscending, Key3:=Range("E2"), _
Order3:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Range("B2").Select
cells(rows.count,1).End(xlup)(2).Select
End Sub

 

[StargateFanFromWork]

Well, I can only guess what your worksheet looks like and where the error
occurs, but try this

Sub SortByDate()
'
'
Application.Goto Reference:="R1C2"
Selection.Sort Key1:=Range("B2"), _
Order1:=xlAscending, Key2:=Range("D2"), _
Order2:=xlAscending, Key3:=Range("E2"), _
Order3:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Range("B2").Select
cells(rows.count,1).End(xlup)(2).Select
End Sub

<sigh> I feel like giving up ... <g> ... Thanks so much for your effort,
it's greatly appreciated. But now, no matter what data there is or isn't,
it's always going to the same cell, in this case A52.

I wish there was an easier way for us to determine syntax, don't you? <g>

Hmmm, I'm going to give this one more try to try to explain.

This syntax, as originally given, works perfectly when there is data in the
sheet. So this particular syntax perhaps doesn't need changing at all:

Sub SortByDate()
'
'
Application.Goto Reference:="R1C2"
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("D2")
_
, Order2:=xlAscending, Key3:=Range("E2"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Range("B2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
End Sub

It's how to properly put in an If statement around this so that after the
first line:

Application.Goto Reference:="R1C2"

the IF statement can be implemented. If there is data, then yes, proceed to
sort.
If there is no data, then a msg box comes up saying "There is no data to
sort." and then to proceed to:

Range("B2").Select

Is this, perhaps, clearer, too?

Thanks.

Regards,
Tom Ogilvy

Sub SortByDate()
'
'
Application.Goto Reference:="R1C2"
if ActiveCell.CurrentRegion.Count < 2 then
msgbox "No data to sort"
exit sub
end if
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, _
Key2:=Range("D2"), _
Order2:=xlAscending, _
Key3:=Range("E2"), _
Order3:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Range("B2").End(xlDown)(2).Select
End Sub

Hi!

It's neat the way you changed the coding to do the same thing as the
recorded code. Cool. It didn't work though, sorry to say. I get the
identical error message when used on a blank sheet. When I put some dates
in, it still takes me to the empty cell below them after sorting, so that's
pretty neat. But the original problem doesn't seem to be dealt with.

I'm using XL2K on W2K, if that makes any difference? Sorry, I should have
said.

Thanks.

[snip]

 

[SidBord]

Well, one way to handle it would be to let the error occur,
then branch to an error handler:

Dim Msg as String
On Error GoTo ErrMsg
....
ErrMsg:
On Error GoTo 0 '<-Re-enable system error detection
Msg = "You fool! You're trying to sort no data!"
Msgbox Msg,vbCritical,"User Macro '(macroname)'"
Exit Sub

I'll leave the details up to you.

 

[Tom Ogilvy]

What isn't clear is what is in the worksheet when you have no data. If it
always ends up at A52, then there is data in the sheet at least in cell A51
(even it it might look blank). What I can't tell you is how to test if
their "is no data" since, based on what you describe, there will never be
all empty cells.

You still have masterfully avoided stating what error you get and what line
of code is highlighted when the error occurs.
--
Regards,
Tom Ogilvy

Well, I can only guess what your worksheet looks like and where the error
occurs, but try this

Sub SortByDate()
'
'
Application.Goto Reference:="R1C2"
Selection.Sort Key1:=Range("B2"), _
Order1:=xlAscending, Key2:=Range("D2"), _
Order2:=xlAscending, Key3:=Range("E2"), _
Order3:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Range("B2").Select
cells(rows.count,1).End(xlup)(2).Select
End Sub

<sigh> I feel like giving up ... <g> ... Thanks so much for your effort,
it's greatly appreciated. But now, no matter what data there is or isn't,
it's always going to the same cell, in this case A52.

I wish there was an easier way for us to determine syntax, don't you? <g>

Hmmm, I'm going to give this one more try to try to explain.

This syntax, as originally given, works perfectly when there is data in the
sheet. So this particular syntax perhaps doesn't need changing at all:

Sub SortByDate()
'
'
Application.Goto Reference:="R1C2"
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("D2")
_
, Order2:=xlAscending, Key3:=Range("E2"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Range("B2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
End Sub

It's how to properly put in an If statement around this so that after the
first line:

Application.Goto Reference:="R1C2"

the IF statement can be implemented. If there is data, then yes, proceed to
sort.
If there is no data, then a msg box comes up saying "There is no data to
sort." and then to proceed to:

Range("B2").Select

Is this, perhaps, clearer, too?

Thanks.

Regards,
Tom Ogilvy

[snip]

 

[StargateFanFromWork]

What isn't clear is what is in the worksheet when you have no data. If it
always ends up at A52, then there is data in the sheet at least in cell A51
(even it it might look blank). What I can't tell you is how to test if
their "is no data" since, based on what you describe, there will never be
all empty cells.

Ah, okay. I see what the problem is. It's hard to know what to explain.
Column A has =ROW()-1 for row numbers. But it's not the data the needs
sorting at all. I didn't realize this would make a difference, but then,
I'm no expert at all.

You still have masterfully avoided stating what error you get and what line
of code is highlighted when the error occurs.

Tom, there are ways of telling people "off" that aren't offensive like this,
you know. Nothing that is done by us users of this kind is deliberate. I
was not being deliberately obtuse, in other words.

No line is highlighted at all. I end up in cell B65536 with the exact error
that I posted. It is helpful when you ask questions, though, to pinpoint
what the problem(s) is/are. We don't now where the troubles lie, obviously.

--
Regards,
Tom Ogilvy

<sigh> I feel like giving up ... <g> ... Thanks so much for your effort,
it's greatly appreciated. But now, no matter what data there is or isn't,
it's always going to the same cell, in this case A52.

I wish there was an easier way for us to determine syntax, don't you?

Hmmm, I'm going to give this one more try to try to explain.

This syntax, as originally given, works perfectly when there is data in the
sheet. So this particular syntax perhaps doesn't need changing at all:

Sub SortByDate()
'
'
Application.Goto Reference:="R1C2"
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("D2")
_
, Order2:=xlAscending, Key3:=Range("E2"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Range("B2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
End Sub

It's how to properly put in an If statement around this so that after the
first line:

Application.Goto Reference:="R1C2"

the IF statement can be implemented. If there is data, then yes,

proceed

to
sort.
If there is no data, then a msg box comes up saying "There is no data to
sort." and then to proceed to:

Range("B2").Select

Is this, perhaps, clearer, too?

Thanks.

Regards,
Tom Ogilvy


Sub SortByDate()
'
'
Application.Goto Reference:="R1C2"
if ActiveCell.CurrentRegion.Count < 2 then
msgbox "No data to sort"
exit sub
end if
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, _
Key2:=Range("D2"), _
Order2:=xlAscending, _
Key3:=Range("E2"), _
Order3:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
Range("B2").End(xlDown)(2).Select
End Sub

Hi!

It's neat the way you changed the coding to do the same thing as the
recorded code. Cool. It didn't work though, sorry to say. I get the
identical error message when used on a blank sheet. When I put some dates
in, it still takes me to the empty cell below them after sorting, so
that's
pretty neat. But the original problem doesn't seem to be dealt with.

I'm using XL2K on W2K, if that makes any difference? Sorry, I

should
have

said.

Thanks.

[snip]

 

[StargateFanFromWork]

Well, one way to handle it would be to let the error occur,
then branch to an error handler:

Dim Msg as String
On Error GoTo ErrMsg
...
ErrMsg:
On Error GoTo 0 '<-Re-enable system error detection
Msg = "You fool! You're trying to sort no data!"
Msgbox Msg,vbCritical,"User Macro '(macroname)'"
Exit Sub

Phew, this is more difficult than I thought it would be. But all this has
been most helpful. The above didn't work either, actually. It didn't
overcome the problems with the simpler syntax I had, believe it or not <g>.

But this helped me to understand better what I need, I think. I believe,
but correct me if I'm wrong, that I need something like this:

IF there is data to sort, do this ... (then my sort macro would be used).

IF there is NO data to sort, do this ... (error box advising there is no
data, and active cell back to B2)

What would I be looking for in terms of structure, pls?

If someone were to direct me to an example, that is perfectly fine by me.
The difficulties with us users out in the field is lack of material and no
knowing the commands to use in given situations so it's virtually impossible
to find in the time we have available. I _have_ broken down and built up my
own code many times in other, albeit much easier and more intuitive
scripting languages than VB, but that's because I either had plenty of files
to study the code from (dBase III+) or had a command builder to work as well
as numerous examples (WordPerfect, Filemaker Pro) or a script helper (i.e.,
one the scriptwriting apps for AutoIt).

Thanks.

I'll leave the details up to you.

Thanks! <g> No, I didn't use

Msg = "You fool! You're trying to sort no data!"

but a much gentler "There is no data to sort." <g>

This is all for a blank master index file that I'm leaving with my
supervisor (I finish this Friday). It's in template form, so when a person
first sees it and clicks the buttons, rather than get an unidentified error
box, they'll get a message saying there's no data, so they know it's a
perfectly good working file.

Cheers!