Reply
Mon 24 Mar, 2008 01:54 pm
I have the following code in a macro, that is intended to have the user select 2 rows from the sheet named Seton, and move it to the sheet named SAustin.
If the user forgets to choose 2 rows, they get a message box "Must select 2 rows", but when two rows are selected, I still get the message.
I need and ELSE command in there somewhere, and I can figure out where, or what the verbage should be.
I tried putting ELSE after the 2nd line, but what happens then is instead of the message box, the marco selects all the rows on the sheet.
Can anyone help? .....Robert Genteel, are you out there?
If TypeName(Selection) <> " " Then
MsgBox "MUST SELECT TWO ROWS"
Exit Sub
End If
Selection.Cut
Sheets("SAustin").Select
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Seton").Select
Selection.Delete Shift:=xlUp
Range("A14:A15").Select
Sheets("SAustin").Select
Cells(Rows.Count, "A").End(xlUp).Offset(0, 0).Select
End Sub
someone answer me dammit!
Ibbitty gibbity....
Someone bright will happen along soon, I have faith...
I don't know VBA, but if the body of your if statement is being executed whether or not you have selected two rows then it is always evaluating to true. So maybe find out why the statement is evaluating to true even when you have two rows selected.
Ok, after the 2nd line I've put
Else: Selection.Rows.Select
I still get the message box, but they can live with that, sez I, since it allows the 2 rows to move.
But, now, if 2 rows aren't selected, I get the message box and it moves the chosen cell to the other page.
I don't know why it's doing that if it's after the IF THEN and following the ELSE.
I suck at VBA
Is the exit sub before the else?
If TypeName(Selection) <> " "
Doesn't this mean that the error message will be displayed if the user has
selected anythimg but a blank? Surely that can't be your intention.
Hi George
Here's my intention, in English (I speak that a little bit better)
If the user forgets to highlight 2 rows on the sheet called Seton, and click the button called SAustin, they will get a message box that says "MUST SELECT TWO ROWS."
If they choose 2 rows on the Seton sheet and click the SAustin button, those 2 rows will be sent to the end of the SAustin sheet.
Very easy.
Like I said, I don't know VBA, but here's how I read your algorithm. If (your condition that I don't know enough to evaluate but should mean the user did not select two rows) then pop up a message and exit the routine. Otherwise, move the rows. You don't actually need an else (I don't think) if you're breaking out of the routine in the body of the if statement. If you Exit Sub then nothing after "Exit Sub" will happen. If the condition evaluates to false, as it should if the user has selected two rows, then the alert and the exit should be skipped and everything after that should be executed.
So if I were you, I would first verify that the condition of the if statement evaluates to true only when the user has not selected two rows. Then I would verify that the routine is exited in that case.
I logically follow you freeduck, but, I don't know how to write it.
That's were I'm messing up.
I hope George knows
It's just those 4 or 5 lines in the beginning.....sigh.
I think what you originally posted is fine but that your condition (If TypeName(Selection) <> " " ) isn't correct and is always evaluating to true. Also, the section you added later (Selection.Rows.Select) probably still needs to go there, but after the "end if" line.
Let me see what else I can figure out by googling.
Ok, so according to this link
http://www.automateexcel.com/index.php/2004/12/09/excel_vba_test_if_selection_is_range your if statement is always true because it's always something other than "", as George said. So you need to find out what to put there so that it is true if the selection is exactly two rows, right? TypeName doesn't seem to be the right thing. I'll keep looking.
Sorry, this is the blind leading the blind, but I've got a pretty trusty cane that's always served me well.
Ok, try this:
If Selection.Rows.Count <> 2
I'll try that first thing in the morning freeduck!
Thanks!
No problem, I hope it works!
I shoulda quit reading this when I bumped it. Now ima be tryin to digest this crap all night in some weird Dagmarka dogshooting hayseed dream...
RH
Run a simple test to see if it is selecting the correct number of columns when you want it to.
If TypeName(Selection) <> " " Then
MsgBox "You have selected the wrong Number of columns"
Else
MsgBox "you have selected 2 columns"
End If
Simple msgboxes will often help you debug statements because you can then test them and get immediate answers while not running the rest of your code.
And always test more than 2 conditions. The code may work fine for zero and 2 in the test but when someone selects 1, 3 or 10 you might get a different result than you intended so test enough conditions.
A simple macro shows that no matter what you select the returned value will always be "range"
Paste the following into a macro and run it with any selection
If TypeName(Selection) <> " " Then
MsgBox "You have selected " & TypeName(Selection)
Else
MsgBox "you have NOT selected " & TypeName(Selection)
End If
Now try Selection.Columns.Count
Edit.. Oops. It looks like Freeduck already answered and its rows.
MsgBox "The number of columns is ' & Selection.Columns.Count
It looks like your statement should be
If Selection.Columns.Count <> 2 Then
You may want to test for a range first
If TypeName(Selection) = "Range" Then
#the happy path
Else
#the error path
Stumble It!
Tweet This
Bookmark on Delicious
Share on Facebook
Share on MySpace