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