Solved: Autofilter and apply cell value to visible cells

[ilyaskazi]

I am not getting the values as wanted below:


Sub Test_AutoFilter()

For i = 1 To ItemCollection.count
'Do autofilter on field=5 (i.e. Column-E) with Criteria1:="=" & ItemCollection.item(i)
'Apply cell value of Column-D = "Found" (only to visible cells and ignoring header row)
Next i

End Sub
I tried lot spending an hour, with applying to whole sheet.

Please see, also posted at:
http://www.ozgrid.com/forum/showthread.php?t=49801

[lucas]

Hello ilyaskazi,
From your post I assume you want to autofilter by column E and then have the formula's in column D only work on the filtered data....if that is correct you may need to copy the filtered data to a new or temorary sheet...could you help us understand what your objective is please.

ps thanks for listing your crosspost, it helps.

[ilyaskazi]

yes steve you are correct.

Here is the little details of what i m trying to do...
I hv collected some names in ItemCollection
Then i am looping all the names of ItemCollection with autofilter on Column-E to be found.
After filtering i want to apply flag on column-D (i.e. to only visible cells and yes ignoring the header row as well)

I m succussfully able to do autofilter through all loops. But the problem is i am not able to flag the found items. It flags to whole sheet damaging my all datas.

Here is the example:

With Sheets("MySheet")
.Rows(1).AutoFilter
For i = 1 To ItemCollection.count
.Rows(1).AutoFilter Field:=CStr(5), _
Criteria1:="=" & ItemCollection.Item(i), Operator:=xlOr '..Filter

.Range("D2:D" & .Range("E" & xLRows).End(xlUp).Row) _
.SpecialCells(xlCellTypeVisible) = "Found" '..Flag
Next i
End With

[Norie]

Do you really need to use AutoFilter for this?

Couldn't you use a formula to display the flag?

[lucas]

So your filtering based on finding an equal sign = in column E
then you want to flag those that are found by putting "Found" beside each one in column E is that correct?

If so then Norie is correct, no need to autofilter unless you have another reason...

[ilyaskazi]

Do you really need to use AutoFilter for this?

Couldn't you use a formula to display the flag?

Hi Norie,
I can't, This is something very big project in which i am stuck with this autofilter loop. Also sheet row goes above 35000, 45000 and so on..

[ilyaskazi]

So your filtering based on finding an equal sign = in column E
then you want to flag those that are found by putting "Found" beside each one in column E is that correct?

If so then Norie is correct, no need to autofilter unless you have another reason...

Hey Steve,

Its not equal sign "=". There is an variable returning its item from its loop.
& ItemCollection.Item(i)

Though ItemCollection loop is applying to the project, I cannot use formula.
Please let me know and how if possible through AutoFilter only.

[ilyaskazi]

Finally guys,

It is solved. Solution: I replaced:
.Range("D2:D" & .Range("E" & xLRows).End(xlUp).Row) _
.SpecialCells(xlCellTypeVisible) = "Found"

with assigning in variable first to loop later:
Filterow = .Range("E" & xLRows).End(xlUp).Row
.Range("D2:D" & FilterRow) _
.SpecialCells(xlCellTypeVisible) = "Found"

[lucas]

Sorry I didn't get back with you sooner....work and all that has kept me busy this morning.

Thanks for posting your solution.

[Norie]

Hi Norie,
I can't, This is something very big project in which i am stuck with this autofilter loop. Also sheet row goes above 35000, 45000 and so on..
And why couldn't you use a formula?:)