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.

Is there a way for it to pull the headers as well? There's quite a bit of data in the rows so it's tough to tell what's what in the new workbook without them.

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.

I wrote:

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),

Correction: typical for Win32.

--
There's this weird kid watching everything that I type
sitting beside me. Yes you. I don't see anyone else.
Go away. Obviously I don't want you to do this.

Auric__ submitted this idea :
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.)

This is more or less what I was eluding to when I said VB[A] converts
returns to fit the function. I think it's really great that you made
the extra effort to better explain some of the nuances most VBA users
wouldn't be aware of since (typically) they do not have the programming
background we do.

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 do the same, usually. The exception would be if the calling procedure
expects/needs a variant for further processing the return. It's
entirely dependant on the 'nature-of-the-beast', IMO!

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

Well.., that wouldn't make sense and so I concur<g!

--
Garry

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

Auric__ used his keyboard to write :
I wrote:

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),

Correction: typical for Win32.

Gotcha!

--
Garry

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

JonathanK1 wrote:

Is there a way for it to pull the headers as well? There's quite a bit
of data in the rows so it's tough to tell what's what in the new
workbook without them.

Sub copier()
Dim TheAnswer As String
Dim working As Worksheet, dumping As Workbook
Set working = ActiveSheet
TheAnswer = LCase$(InputBox("State?"))
Set dumping = Workbooks.Add
'***YOU MUST ADJUST THIS LINE***
For x = 1 To (number of header rows)
working.Rows(x).EntireRow.Copy
dumping.Activate
ActiveSheet.Paste
ActiveCell.Offset(1).Select
Next
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

--
- I expect you know best, as usual.
- I do -- when I know anything.

Excellent. Thanks again!

JonathanK1 wrote:

Auric__;1609865 Wrote:
JonathanK1 wrote:
-
Is there a way for it to pull the headers as well? There's quite a
bit
of data in the rows so it's tough to tell what's what in the new
workbook without them.-

Sub copier()
Dim TheAnswer As String
Dim working As Worksheet, dumping As Workbook
Set working = ActiveSheet
TheAnswer = LCase$(InputBox("State?"))
Set dumping = Workbooks.Add
'***YOU MUST ADJUST THIS LINE***
For x = 1 To (number of header rows)
working.Rows(x).EntireRow.Copy
dumping.Activate
ActiveSheet.Paste
ActiveCell.Offset(1).Select
Next
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

Excellent. Thanks again!

No prob. I suggest you read thru what Garry ("GS") wrote; he has a point
about using Variants.

--
I never killed anyone. I avoid going over that edge by
writing about a guy who has taken a flying leap over it.

Me again. So...this worked, but I'm wondering if I can do more. It might get confusing if I do, but I want to follow the rules, so just let me know. I apologize if I'm in the wrong by tagging on to this.

The data from column H shows up in a new workbook, as I needed. But I'd like it sorted a certain way. For instance, column C has north, south, east and west and column M has New and Old, but I'd like these separated in the new workbook (still sorted by H).

For instance... the new workbook would have just the State Searched (Texas, for instance), but would take the Texas results and break them down by location (N, S, E, W) and age (old vs. New). Is this possible, or am I asking for too much out of this?

Thanks!

J

JonathanK1 wrote:

Me again. So...this worked, but I'm wondering if I can do more. It
might get confusing if I do, but I want to follow the rules, so just let
me know. I apologize if I'm in the wrong by tagging on to this.

The data from column H shows up in a new workbook, as I needed. But I'd
like it sorted a certain way. For instance, column C has north, south,
east and west and column M has New and Old, but I'd like these separated
in the new workbook (still sorted by H).

For instance... the new workbook would have just the State Searched
(Texas, for instance), but would take the Texas results and break them
down by location (N, S, E, W) and age (old vs. New). Is this possible,
or am I asking for too much out of this?

The Range.Sort method does this. Add this to the end of the sub, right above
"End Sub":

Cells.Sort Key1:=Range("C:C"), Order1:=xlAscending, _
Key2:=Range("M:M"), Order2:=xlAscending, Header:=xlYes

However, this can only sort using a maximum of 3 keys. If you need to sort
with more keys than that (which happens to me *all the damn time*) you need
to use the Sort object. For info on that, start with the helpfile's entry
(search for "Sort Object").

--
It's liking fighting in the dark.

That worked - thank you! I tried to add one more key and it kept giving me an error (I wanted to add the Q column). I tried it like you did but changed the range, like this:

Key3:=Range("Q:Q"), Order3:=xlAscending, Header:=xlYes

I tried tweaking it (removing header etc.) and it won't run, just sorts the first two "keys." I'm sure I'm being a bonehead here, but you said I could do up to three...so it must be the formatting of it that I'm getting wrong.

Did I mention you folks are awesome?

J-

Ok, so this isn't working right now....and I can't figure out why. Here is what I have.

Sub Button6_Click()
Dim TheAnswer As String
Dim working As Worksheet, dumping As Workbook
Set working = ActiveSheet
TheAnswer = LCase$(InputBox("Enter State below"))
Set dumping = Workbooks.Add
For x = 1 To 17
working.Rows(x).EntireRow.Copy
dumping.Activate
ActiveSheet.Paste
ActiveCell.Offset(1).Select
Next
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
Cells.Sort Key1:=Range("C:C"), Order1:=xlAscending, _
Key2:=Range("Q:Q"), Order2:=xlDescending, Header:=xlYes
End Sub

First, when it pulls the data to the new workbook from H column (the answer to the question), it's giving more than just the answers I want - there are wrong ones mixed in. Instead of giving me all NY for example (like I input for the answer), I'm getting some others in there as well, which shouldn't happen.

Also, for column C (sort at the bottom) I'd like certain results to show first. Is this possible? So these are states and I'd like NY, MN, SC and UT to come first - the rest can go in any order after that.

See if the following rework gets the data correctly copied over...

Sub Button6_Click()
Dim wksSource As Worksheet, wksTarget As Worksheet
Dim vAns, n&, lNextRow&

Set wksSource = ActiveSheet
vAns = UCase$(InputBox("Enter the State abbreviation"))
If vAns = "" Then Exit Sub

Workbooks.Add: Set wksTarget = ActiveSheet
wksSource.Range("1:17").Copy wksTarget.Cells(1)
lNextRow = wksTarget.UsedRange.Rows.Count + 1

For n = 1 To wksSource.UsedRange.Rows.Count
If UCase$(wksSource.Cells(n, 8).Value) = vAns Then
wksSource.Rows(n).Copy wksTarget.Rows(lNextRow)
lNextRow = lNextRow + 1
End If
Next 'n
Application.CutCopyMode = False

With wksTarget
.Cells.Sort Key1:=.Columns("C"), Order1:=xlAscending, _
Key2:=.Columns("Q"), Order2:=xlDescending, _
Header:=xlYes
End With 'wksTarget
End Sub

...and if it does then we'll look at the custom sort.

--
Garry

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

Unfortunately, this isn't working. There is an error on "wksSource.Rows(n).CopywksTarget.rows(1NextRow )"

Also, it's pulling data other than what I asked it to (not in answer to the question). The original way I was doing it worked aside from a) a few other answers were getting pulled in (not sure why) and b) I wasn't able to indicate the order of the column "C" sort.

Sorry if I'm being a pain here, folks. This has been driving me nuts for weeks.

This may be way out there, but would it be easier/better to have a userform where the questions are answers (fields are completed) and the search command button pulled the data based on that and pasted it into a new workbook? I don't have much experience with userforms...just thinking outloud here.

I figured this out but my question has changed a bit so I'm going to start a new thread. Thanks!