Accessing rows after AutoFilter

[MartynGriffin]

I searched 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 value 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 be any row number. I even tried selecting the cell on row 1 (headings) and then using Application.Sendkeys("{DOWN}"), but no workee!

Here is a snipit of code:

Sub SelectItem(Col1 As String, CriteriaValue As String, MatchCol As 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
str = UCase(Mid(TgtCol, 1, 1))
tgtid = 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


--
Jim
"MartynGriffin" <[email protected]>
wrote in message
news:[email protected]...
>
> 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 value
> 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 be
> any row number. I even tried selecting the cell on row 1 (headings) and
> then using Application.Sendkeys("{DOWN}"), but no workee!
>
> Here is a snipit of code:
>
>> Sub SelectItem(Col1 As String, CriteriaValue As String, MatchCol As
>> 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!!!!
>
>
> --
> MartynGriffin
> ------------------------------------------------------------------------
> MartynGriffin's Profile:
> http://www.excelforum.com/member.php...o&userid=30496
> View this thread: http://www.excelforum.com/showthread...hreadid=501497
>

[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
> > Selection.AutoFilter Field:=colid, Criteria1=CriteriaValue
'---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" <[email protected]> wrote
in message news:[email protected]...
>
> 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 value
> 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 be
> any row number. I even tried selecting the cell on row 1 (headings) and
> then using Application.Sendkeys("{DOWN}"), but no workee!
>
> Here is a snipit of code:
>
> > Sub SelectItem(Col1 As String, CriteriaValue As String, MatchCol As
> > 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!!!!
>
>
> --
> MartynGriffin
> ------------------------------------------------------------------------
> MartynGriffin's Profile:
http://www.excelforum.com/member.php...o&userid=30496
> View this thread: http://www.excelforum.com/showthread...hreadid=501497
>

[MartynGriffin]

Thanks,

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