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.

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

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

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