Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I use AutoFilter without supressing rows ? | Excel Discussion (Misc queries) | |||
AutoFilter and adding new rows | Excel Discussion (Misc queries) | |||
accessing rows with macros | Excel Discussion (Misc queries) | |||
delete rows autofilter | Excel Programming | |||
AutoFilter and Hidden Rows | Excel Programming |