Hi all,

I have a macro that prompts an input:

Dim TheAnswer$
TheAnswer = Inputbox(

Etc. etc. You get the gist. Anyway, it takes the input but doesn't do anything with it. I have one colmn in the spreadsheet that has offices (e.g., Texas, Ohio etc.). Its column H. I'd like the input to grab the rows that have the input I provide and past them into a new book/spreadsheet. Is this possible?

So, there may be twenty rows with Texas mixed in. I want all of these pasted into the new spreadsheet. So when I click the button it'll ask me for the input. I enter Texas and it gives me all the rows with Texas in the new book/spreadsheet. Sorry if I'm overexplaining.

Can you folks help?

Thanks,

J-

JonathanK1 wrote:

I have a macro that prompts an input:

Dim TheAnswer$
TheAnswer = Inputbox(

Etc. etc. You get the gist. Anyway, it takes the input but doesn't do
anything with it. I have one colmn in the spreadsheet that has offices
(e.g., Texas, Ohio etc.). Its column H. I'd like the input to grab the
rows that have the input I provide and past them into a new
book/spreadsheet. Is this possible?

So, there may be twenty rows with Texas mixed in. I want all of these
pasted into the new spreadsheet. So when I click the button it'll ask
me for the input. I enter Texas and it gives me all the rows with Texas
in the new book/spreadsheet. Sorry if I'm overexplaining.

Here's a 5-minute hack; see if it works for you:

Sub copier()
Dim TheAnswer As String
Dim working As Worksheet, dumping As Workbook
Set working = ActiveSheet
TheAnswer = LCase$(InputBox("State?"))
Set dumping = Workbooks.Add
For x = 1 To working.Cells.SpecialCells(xlCellTypeLastCell).Row
If LCase$(working.Cells(x, 8).Value) = TheAnswer Then
working.Rows(x).EntireRow.Copy
dumping.Activate
ActiveSheet.Paste
ActiveCell.Offset(1).Select
End If
Next
Application.CutCopyMode = False
End Sub

--
This is why my characters usually end up being impossible for me to like:
My curiosity leads them into actions I can't condone.

<FWIW
You should *always* use a variant when prompting for user input because
if the user cancels you'll raise an error because the return for a
cancel isn't a string. Usually it returns *False* in most cases,
including browse dialogs...

Dim vAns As Variant
OR
Dim vAns

...or in the case of a MsgBox vbYesNoCancel, vAns will return 3
different values; vbYes (6), vbNo (7), vbCancel (2)! While all of these
are type Long you never have to worry about the return being a type
mismatch.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS wrote:

<FWIW
You should *always* use a variant when prompting for user input because
if the user cancels you'll raise an error because the return for a
cancel isn't a string. Usually it returns *False* in most cases,
including browse dialogs...

Dim vAns As Variant
OR
Dim vAns

..or in the case of a MsgBox vbYesNoCancel, vAns will return 3
different values; vbYes (6), vbNo (7), vbCancel (2)! While all of these
are type Long you never have to worry about the return being a type
mismatch.

If the user cancels the inputbox and the result is assigned to a string, the
string is left empty. I've *never* had a problem with it. This:

Dim x As String
x = InputBox("Foo")
MsgBox "*" & x & "*" & Err.Number

....works as expected under Excel 2007, VB6, and VB4 (16-bit): the msgbox
displays "**0" in all three environments.

--
I used to be a narrator for bad mimes.
-- Steven Wright

For clarity...

Firstly, my reply wasn`t meant for you! Sorry.., my bad!
Secondly, I wasn't stating a hard rule, only my opinion! In hindsight
it would have been better had I started out with...

"I suggest to always use a variant...because..."

However, be it that you are absolutely correct in your example, there
are uses that VB[A] will convert for us. As programmers we would know
when/where. I was trying to convey to the many non-programmers here
that using a variant obviates any and all chances of a type error being
raised.

Perhaps my MsgBox example wasn`t a good choice since it happens that VB
does convert that too.<g In the case of InputBox a string is returned.
In the case of MsgBox an Integer (contrary to my claim of Long) is
returned. In the case of browser dialogs a string is returned *if*
there's a SelectedItem; Cancel returns a boolean and so type mismatch
happens if your variable isn't type variant. In the case of an API
function the return could be anything depending on the def of the
function. Thus, I've just made it a practice to use variants in this
context.

I can see, though, how one might expect a string in all cases but that
just isn't so!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Thanks, this worked perfectly. The only downside (and I have no idea if this ties in to what you two have been chatting about), is that if I cancel it (when prompted), the workbook opens anyway (blank of course). Not the end of the world. I appreciate everyone's help.

GS wrote:

For clarity...

Firstly, my reply wasn`t meant for you! Sorry.., my bad!
Secondly, I wasn't stating a hard rule, only my opinion! In hindsight
it would have been better had I started out with...

"I suggest to always use a variant...because..."

However, be it that you are absolutely correct in your example, there
are uses that VB[A] will convert for us. As programmers we would know
when/where. I was trying to convey to the many non-programmers here
that using a variant obviates any and all chances of a type error being
raised.

Ok, that makes sense. I withdraw my argumentative post. ;-)

Perhaps my MsgBox example wasn`t a good choice since it happens that VB
does convert that too.<g In the case of InputBox a string is returned.
In the case of MsgBox an Integer (contrary to my claim of Long) is
returned.

Actually, it's kind of interesting. This:

MsgBox VarType(MsgBox("test", vbYesNoCancel))

....tells me that the return type is Long (vartype 3)... but the definition
in VBAEN32.OLB is like so:

short _stdcall MsgBox(
[in] VARIANT* Prompt,
[in] VARIANT* Buttons,
[in] VARIANT* Title,
[in] VARIANT* HelpFile,
[in] VARIANT* Context);

On Windows, a C "short" is usually 16 bits, a.k.a. Integer. So it looks
like Windows is taking that short and putting it into a 32-bit var before
returning it (which I guess is typical for Windows), then VB(A) does its
usual magic and makes the result fit where it's needed.

I suppose it's left over from the 16-bit versions of VB. (In VB4 16-bit,
the above vartype line says that MsgBox is indeed an Integer, vartype 2.)

In the case of browser dialogs a string is returned *if*
there's a SelectedItem; Cancel returns a boolean and so type mismatch
happens if your variable isn't type variant. In the case of an API
function the return could be anything depending on the def of the
function. Thus, I've just made it a practice to use variants in this
context.

I usually make the vartype match the definition of the function, especially
with API calls. If the function is declared Long, chances are I'm assigning
it to a Long, not a Variant. (Assuming I'm catching the return value at
all, of course.)

I can see, though, how one might expect a string in all cases but that
just isn't so!

Well... I can't say I've ever expected a string from a msgbox. ;-)

--
Zero-tolerance is a political buzz-word,
not a legitimate engineering specification.