Accessing rows after AutoFilter

[MartynGriffin]

I search the forum for this info, but had no luck, so here goes:

Using VBA (or VB6 externally) I need to do the following:
1) Turn on autofilters
2) Run an autofilter on column "A" looking for any rows with "xxx"
3) On the first row found, extract the value from column "C"
4)Re-autofilter all rows to find those that contain the extracted valu
in column "D"

Seems simple enough, and I have it all working, EXCEPT for part 3)
I cant quite figure out how to select the first row found, which may b
any row number. I even tried selecting the cell on row 1 (headings) an
then using Application.Sendkeys("{DOWN}"), but no workee!

Here is a snipit of code:

Sub SelectItem(Col1 As String, CriteriaValue As String, MatchCol A
String,TgtCol as String)

Dim str As String
Dim colid As Long
Dim common As String
Dim matchid As Long
Dim tgtid As Long

'Convert alpha columns to numeric
str = UCase(Mid(Col1, 1, 1))
colid = Asc(str) - Asc("A") + 1
str = UCase(Mid(MatchCol, 1, 1))
matchid = Asc(str) - Asc("A") + 1
Selection.AutoFilter Field:=colid, Criteria1=CriteriaValue

*' The fuzzy part to get value from first row found
'--------------------------------------
Cells(1, matchid).Select
Application.SendKeys ("{DOWN}")
'--------------------------------------* common = Selection.Value
'Reset original selection
Selection.AutoFilter Field:=colid
'Make calculated selection
Selection.AutoFilter Field:=tgtid, Criteria1:=common
End Sub

Can anyone wake me up!!!

 

[Jim Rech]

I think using autofilter to find "xxx" is not the way to go. Rather:

Sub a()
Dim CellOffset As Long
Err.Clear
On Error Resume Next
CellOffset = Application.Match("xxx", Range("A:A"), False)
If Err.Number = 0 Then
MsgBox Cells(CellOffset, 3).Value
Else
MsgBox "No match"
End If
End Sub

 

[MartynGriffin]

Unfortunately, both the first and the second autofilter can return
several rows, and I need to process these as a group. The snippet of
code I supplied does not show that, it was just meant to clarify what I
was trying to do. And that was "How do I select any of the cells that
were returned from any specific autofilter operation?"

 

[kounoike]

only about 3)
after first filtering'---insert this code
For Each s In Columns(str).SpecialCells(xlCellTypeVisible)
If s.Row <> 1 Then
MsgBox cells(s.Row,"c").Value
Exit For
End If
Next

try this would work or not.

keizi

 

[MartynGriffin]

Thanks,

This -*looks*- like the construct I need to process the filtered cells.
Will try it when I get home tonight.