String variable name is incorrectly used literally
 

I have the following autofilter statement in my macro:

Selection.AutoFilter Field:=1, Criteria1:="=*SKU*", Operator:=xlAnd

The term "SKU" in this statment is Dimensioned as a string variable
but it is being used literally. That is the autofilter is selecting
lines that contain SKU.

How do I get this autofilter statement to understand SKU is a string
variable?

Thank you.


John Keith

String variable name is incorrectly used literally
 

Do you need SKU dimensioned as a string variable someplace else? If so, I
think I'd change it so that it's mySKU or something like that so that you
know it's the variable.
--
HTH,
Barb Reinhardt




"John Keith" wrote:

I have the following autofilter statement in my macro:

Selection.AutoFilter Field:=1, Criteria1:="=*SKU*", Operator:=xlAnd

The term "SKU" in this statment is Dimensioned as a string variable
but it is being used literally. That is the autofilter is selecting
lines that contain SKU.

How do I get this autofilter statement to understand SKU is a string
variable?

Thank you.


John Keith

String variable name is incorrectly used literally
 

This works with me:

Selection.AutoFilter Field:=1, Criteria1:=SKU, Operator:=xlAnd


RBS


"John Keith" wrote in message
...
I have the following autofilter statement in my macro:

Selection.AutoFilter Field:=1, Criteria1:="=*SKU*", Operator:=xlAnd

The term "SKU" in this statment is Dimensioned as a string variable
but it is being used literally. That is the autofilter is selecting
lines that contain SKU.

How do I get this autofilter statement to understand SKU is a string
variable?

Thank you.


John Keith

String variable name is incorrectly used literally
 

On Sun, 14 Sep 2008 06:07:01 -0700, Barb Reinhardt
wrote:

Do you need SKU dimensioned as a string variable someplace else? If so, I
think I'd change it so that it's mySKU or something like that so that you
know it's the variable.

Barb,

Wow! What a quick response.

I do use the variable one other time, but I can eaily change the name.

But changing the variable name does not solve the problem, the
autofilter now thinks it is searching for mySKU.


John Keith

String variable name is incorrectly used literally
 

In this line, you are searching for somethng that contains SKU. Don't
change this one.

Selection.AutoFilter Field:=1, Criteria1:="=*SKU*", Operator:=xlAnd

If you refer to SKU someplace else, use mySKU or something so that it knows
they are different.
--
HTH,
Barb Reinhardt




"RB Smissaert" wrote:

This works with me:

Selection.AutoFilter Field:=1, Criteria1:=SKU, Operator:=xlAnd


RBS


"John Keith" wrote in message
...
I have the following autofilter statement in my macro:

Selection.AutoFilter Field:=1, Criteria1:="=*SKU*", Operator:=xlAnd

The term "SKU" in this statment is Dimensioned as a string variable
but it is being used literally. That is the autofilter is selecting
lines that contain SKU.

How do I get this autofilter statement to understand SKU is a string
variable?

Thank you.


John Keith

String variable name is incorrectly used literally
 

On Sun, 14 Sep 2008 14:10:32 +0100, "RB Smissaert"
wrote:

This works with me:

Selection.AutoFilter Field:=1, Criteria1:=SKU, Operator:=xlAnd


In this case the filter does recognize "SKU" as a variable but this
criteria is for if the cell equals the value of SKU. I need the
criteria to be contains the value of "SKU". That is why my first post
had the asterisk on either side of "SKU".

Any one else have a suggestion?



John Keith

String variable name is incorrectly used literally
 

Now that I think about this, I'm wondering if you are trying to find cells
that are equal to whatever is in your variable SKU. Can you clarify what you
want.

Barb Reinhardt




"John Keith" wrote:

I have the following autofilter statement in my macro:

Selection.AutoFilter Field:=1, Criteria1:="=*SKU*", Operator:=xlAnd

The term "SKU" in this statment is Dimensioned as a string variable
but it is being used literally. That is the autofilter is selecting
lines that contain SKU.

How do I get this autofilter statement to understand SKU is a string
variable?

Thank you.


John Keith

String variable name is incorrectly used literally
 

On Sun, 14 Sep 2008 11:51:01 -0700, Barb Reinhardt
wrote:

In this line, you are searching for somethng that contains SKU. Don't
change this one.

Selection.AutoFilter Field:=1, Criteria1:="=*SKU*", Operator:=xlAnd

If you refer to SKU someplace else, use mySKU or something so that it knows
they are different.


Barb,

Thanks for following up, but the above command is still interpreting
"SKU" as the item I am trying to filter on rather than the value of
'SKU". (This line is at the end of my macro right now, and when I go
to check the filter settings it says it was looking for rows that
contains "SKU" in the first column.

But I'm confused about something else. The string "SKU" never appears
in the column I am filtering but all of my original rows still are
visible, so it's as if no filtering was done.

I'm still confused.


John Keith

String variable name is incorrectly used literally
 

On Sun, 14 Sep 2008 12:02:01 -0700, Barb Reinhardt
wrote:

Now that I think about this, I'm wondering if you are trying to find cells
that are equal to whatever is in your variable SKU. Can you clarify what you
want.

Barb,

Yes, I am trying to find rows that contain the value of the variable
SKU.



John Keith

String variable name is incorrectly used literally
 

Ah, yes, didn't read it properly.

RBS


"John Keith" wrote in message
...
On Sun, 14 Sep 2008 14:10:32 +0100, "RB Smissaert"
wrote:

This works with me:

Selection.AutoFilter Field:=1, Criteria1:=SKU, Operator:=xlAnd


In this case the filter does recognize "SKU" as a variable but this
criteria is for if the cell equals the value of SKU. I need the
criteria to be contains the value of "SKU". That is why my first post
had the asterisk on either side of "SKU".

Any one else have a suggestion?



John Keith

String variable name is incorrectly used literally
 

try Criteria1:="=*" & SKU & "*",

--


Gary


"John Keith" wrote in message
...
On Sun, 14 Sep 2008 12:02:01 -0700, Barb Reinhardt
wrote:

Now that I think about this, I'm wondering if you are trying to find cells
that are equal to whatever is in your variable SKU. Can you clarify what you
want.

Barb,

Yes, I am trying to find rows that contain the value of the variable
SKU.



John Keith

String variable name is incorrectly used literally
 

On Sun, 14 Sep 2008 15:31:45 -0400, "Gary Keramidas"
<GKeramidasATmsn.com wrote:

try Criteria1:="=*" & SKU & "*",

Gary,

The autofilter settings now reflect the contents of the string
variable so that is a big step forward. (Thanks, how does one figure
out the correct syntax for somethig like this????)

But...... as noted in in another reply from me the filter does not
appear to be working as all the original rows still appear. I'll go
experiment so more but if anyone else has a suggestion I'm all ears.



John Keith

String variable name is incorrectly used literally
 

Recording a macro showed me the right syntax:

Sub test()

Dim str As String

str = "g"

Selection.AutoFilter Field:=1, Criteria1:="=**" & str & "**"

End Sub


RBS


"John Keith" wrote in message
...
On Sun, 14 Sep 2008 15:31:45 -0400, "Gary Keramidas"
<GKeramidasATmsn.com wrote:

try Criteria1:="=*" & SKU & "*",

Gary,

The autofilter settings now reflect the contents of the string
variable so that is a big step forward. (Thanks, how does one figure
out the correct syntax for somethig like this????)

But...... as noted in in another reply from me the filter does not
appear to be working as all the original rows still appear. I'll go
experiment so more but if anyone else has a suggestion I'm all ears.



John Keith

String variable name is incorrectly used literally
 

On Sun, 14 Sep 2008 14:12:50 -0600, John Keith wrote:

But...... as noted in in another reply from me the filter does not
appear to be working as all the original rows still appear. I'll go
experiment so more but if anyone else has a suggestion I'm all ears.


The filter is not working even when I manually go through the process
so something else is clearly going on.

Thanks for all the suggestions.

John Keith

String variable name is incorrectly used literally
 

On Sun, 14 Sep 2008 21:37:28 +0100, "RB Smissaert"
wrote:

Recording a macro showed me the right syntax:

Sub test()

Dim str As String

str = "g"

Selection.AutoFilter Field:=1, Criteria1:="=**" & str & "**"

End Sub

RB,

Most of my macros have been created by recording so I certainly
appreciate the power of that process. And here maybe you can teach me
how to make even better use of recording. My understanding of
recording is to manually go through the steps I want to record (pretty
basic.) But with that process how do you assign a value to a string
and then how would you set up a filter to then use that string????

(teach me how to fish)



John Keith

String variable name is incorrectly used literally
 

post some examples of your data

--


Gary


"John Keith" wrote in message
...
On Sun, 14 Sep 2008 14:12:50 -0600, John Keith wrote:

But...... as noted in in another reply from me the filter does not
appear to be working as all the original rows still appear. I'll go
experiment so more but if anyone else has a suggestion I'm all ears.


The filter is not working even when I manually go through the process
so something else is clearly going on.

Thanks for all the suggestions.

John Keith

String variable name is incorrectly used literally
 

On Sun, 14 Sep 2008 19:10:10 -0400, "Gary Keramidas"
<GKeramidasATmsn.com wrote:

post some examples of your data

Sure.

A2:KT283Ux, KR490En
A3:KT283Ux, KR490En
A4:KT283Ux, KR490En
A5:FG570E, KR489E
A6:FG570E, KR489E

And further down

A164:GA732Ux, GX993Uc, KT283Ux
A165:KU004AA
A166:RJ436Ea, RJ437Ea, RJ438Ea, GD760Ea, KR501Ea

And the search string variable was SKU="KT283U" and the search
criteria was "cell contains the string".

But here's some more info about the failure to filter.

Row 1 contains my column headings and when the filter is enabled the
drop down boxes appears in this row as epxected.

Rows 2-6 appear to be the only rows that the filter is applied to
(there are 311 rows of data.) And the filter does work on rows 2-6 as
rows 2-4 do contain the string I am searching for and are displayed
and rows 5-6 are not displayed. So the problem seems to be that the
filter is not being applied to all the rows of data.




John Keith

String variable name is incorrectly used literally
 

On Sun, 14 Sep 2008 18:21:10 -0600, John Keith wrote:


So the problem seems to be that the
filter is not being applied to all the rows of data.

I think I just found the problem. There are some blank rows in ther
data and it looks like the filter stops at the first blank row.

Let me go test!

Thanks everyone.


John Keith

String variable name is incorrectly used literally
 

use something like this

Sub test()
Dim ws As Worksheet
Dim lastrow As Long
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

With ws.Range("A1:C" & lastrow)
.AutoFilter Field:=1, Criteria1:="0", Operator:=xlAnd
End With
End Sub


--


Gary


"John Keith" wrote in message
...
On Sun, 14 Sep 2008 18:21:10 -0600, John Keith wrote:


So the problem seems to be that the
filter is not being applied to all the rows of data.

I think I just found the problem. There are some blank rows in ther
data and it looks like the filter stops at the first blank row.

Let me go test!

Thanks everyone.


John Keith