String variable name is incorrectly used literally

[John Keith]

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
(e-mail address removed)

 

[Barb Reinhardt]

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.

 

[RB Smissaert]

This works with me:

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


RBS

 

[John Keith]

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
(e-mail address removed)

 

[Barb Reinhardt]

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.

 

[John Keith]

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
(e-mail address removed)

 

[Barb Reinhardt]

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]

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
(e-mail address removed)

 

[John Keith]

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
(e-mail address removed)

 

[RB Smissaert]

Ah, yes, didn't read it properly.

RBS

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
(e-mail address removed)

 

[Gary Keramidas]

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

--


Gary

Barb,

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



John Keith
(e-mail address removed)

 

[John Keith]

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
(e-mail address removed)

 

[RB Smissaert]

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]

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
(e-mail address removed)

 

[John Keith]

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
(e-mail address removed)

 

[mikerickson]

Dim SKU as String
...
Selection.AutoFilter Field:=1, Criteria1:="*" & SKU & "*", Operator:=xlAnd

 

[Gary Keramidas]

post some examples of your data

--


Gary

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
(e-mail address removed)

 

[John Keith]

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
(e-mail address removed)

 

[John Keith]

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
(e-mail address removed)

 

[Gary Keramidas]

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