Input, copy, new book?

[JonathanK1]

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 d
anything with it. I have one colmn in the spreadsheet that has office
(e.g., Texas, Ohio etc.). Its column H. I'd like the input to grab th
rows that have the input I provide and past them into a ne
book/spreadsheet. Is this possible?

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

Can you folks help?

Thanks,

J

 

[Auric__]

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

 

[GS]

<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

 

[Auric__]

<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.

 

[GS]

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

 

[JonathanK1]

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

 

[Auric__]

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. ;-)

 

[Auric__]

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.

 

[GS]

Auric__ submitted this idea :

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

 

[GS]

Auric__ used his keyboard to write :

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

 

[Auric__]

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

 

[JonathanK1]

JonathanK1 wrote:
-

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

 

[Auric__]

Excellent. Thanks again!

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

 

[JonathanK1]

Me again. So...this worked, but I'm wondering if I can do more. I
might get confusing if I do, but I want to follow the rules, so just le
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'
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 separate
in the new workbook (still sorted by H).

For instance... the new workbook would have just the State Searche
(Texas, for instance), but would take the Texas results and break the
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!

 

[Auric__]

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").

 

[JonathanK1]

JonathanK1 wrote:
-

The Range.Sort method does this. Add this to the end of the sub, righ
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 t
sort
with more keys than that (which happens to me *all the damn time*) yo
need
to use the Sort object. For info on that, start with the helpfile'
entry
(search for "Sort Object").

That worked - thank you! I tried to add one more key and it kept givin
me an error (I wanted to add the Q column). I tried it like you did bu
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 sort
the first two "keys." I'm sure I'm being a bonehead here, but you sai
I could do up to three...so it must be the formatting of it that I'
getting wrong.

Did I mention you folks are awesome?

J

 

[JonathanK1]

Ok, so this isn't working right now....and I can't figure out why. Her
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 (th
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 exampl
(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 sho
first. Is this possible? So these are states and I'd like NY, MN, S
and UT to come first - the rest can go in any order after that

 

[GS]

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