Accessing rows after AutoFilter
 

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!!!

--
MartynGriffi
-----------------------------------------------------------------------
MartynGriffin's Profile: http://www.excelforum.com/member.php...fo&userid=3049
View this thread: http://www.excelforum.com/showthread.php?threadid=50149

Accessing rows after AutoFilter
 

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"
wrote in message
news:MartynGriffin.21ouf0_1137348902.2114@excelfor um-nospam.com...

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

Accessing rows after AutoFilter
 

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?"

:(


--
MartynGriffin
------------------------------------------------------------------------
MartynGriffin's Profile: http://www.excelforum.com/member.php...o&userid=30496
View this thread: http://www.excelforum.com/showthread...hreadid=501497

Accessing rows after AutoFilter
 

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" wrote
in message news:MartynGriffin.21ouf0_1137348902.2114@excelfor um-nospam.com...

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

Accessing rows after AutoFilter
 

Thanks,

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


--
MartynGriffin
------------------------------------------------------------------------
MartynGriffin's Profile: http://www.excelforum.com/member.php...o&userid=30496
View this thread: http://www.excelforum.com/showthread...hreadid=501497