R
Rajesh
I am pretty new to Excel Object Model. I need some help.
I have two worksheets that have two columns in both where data has to be
matched and non-matching data have to be marked out in one of the sheets.
Say I have sA and sB as two worksheets. sA has unsorted data and sB has
sorted data. A Pictorial representation would say it better:
Worksheet sA: Worksheet sB:
Matched/Not Matched
Col1 --- Col2 --- Col3 Col1 --- Col2 --- Col3
122 AB01 1 AB01 1 122 Matched
120 AB02 1 2 136 Not Matched
89 AB03 1 3 77 Matched
134 AB01 2 AB02 1 120 Matched
189 AB03 2 2 77 Matched
77 AB02 2 AB03 1 88 Not Matched
77 AB01 3 2 189
Now, I need to first match sB.Col1 with sA.Col2 and if matched get all the
rows for the matched value from sA and sort them on Col2, Col3 to have them
arranged in order like sB for the next step -which is comparing the set of
values in sB.Col3 and sB.Col2 with sA.Col1 and sA.Col3
To do this, I am using the following steps, taking just one value from
sB.Col1, as an example:
'STEP-1 : Find MasterID in Parent set
Set rng1 = sA.Range("A:A").Find(sB.Col1.cell(2,1), , , xlWhole)
if not rng1 is Nothing then
' STEP-2 : Filter out all rows with matching MasterID from the Parent set
sA.Range("A1:Q5000").AutoFilter( _
Field:=13, _
Criteria1:=sB.Col1.cell(2,1) , _
Visibledropdown:=False)
set rng2 = sA.autofilter.range
'set rng2 = sA.autofilter.range.specialcells(xlVisible)
' Get the number of rows filtered
intFilteredRowsCount = rng2.Columns(13).SpecialCells(xlVisible).Count
if not rng2 is nothing then
' STEP-3 : Find the ChildID in the filtered rows of Parent set.
Set rng3 = rng2.Range("A1:A" & intFilteredRowsCount ).Find ( _
sB.Col3.Cells(2,3), _
, _
, _
xlWhole,
xlByColumns, xlNext)
end if
end if
I expected the "rng2" to have cells/rows only that met the filter
criterion, but though the worksheet sA displays only the filtered rows, the
find function in STEP-3 is searching the entire worksheet as if there was no
filter.
Also when I debug.print the value of rng2.cells(2,1) I would expect the
value of the first cell in the first row in set of of filtered rows, but
instead I get the value of the cell in column 1 of row 2 when the sheet was
un-filtered.
I appologise for the long body of this post. I hope I have been clear in
stating my problem -
1. Why is the rng2 visibly filtered, but not accessible as filtered set?
2. Why does the value of rng2.cell(R, C) correspond to the value of R, C
used range of the worksheet despite the fact that rng2 represents a range
which should hold a set of filtered rows?
3. How should I access the set of resulting data after I apply the
autofilter method on a range?
Your help would be deeply appreciated. Thanks, Rajesh
I have two worksheets that have two columns in both where data has to be
matched and non-matching data have to be marked out in one of the sheets.
Say I have sA and sB as two worksheets. sA has unsorted data and sB has
sorted data. A Pictorial representation would say it better:
Worksheet sA: Worksheet sB:
Matched/Not Matched
Col1 --- Col2 --- Col3 Col1 --- Col2 --- Col3
122 AB01 1 AB01 1 122 Matched
120 AB02 1 2 136 Not Matched
89 AB03 1 3 77 Matched
134 AB01 2 AB02 1 120 Matched
189 AB03 2 2 77 Matched
77 AB02 2 AB03 1 88 Not Matched
77 AB01 3 2 189
Now, I need to first match sB.Col1 with sA.Col2 and if matched get all the
rows for the matched value from sA and sort them on Col2, Col3 to have them
arranged in order like sB for the next step -which is comparing the set of
values in sB.Col3 and sB.Col2 with sA.Col1 and sA.Col3
To do this, I am using the following steps, taking just one value from
sB.Col1, as an example:
'STEP-1 : Find MasterID in Parent set
Set rng1 = sA.Range("A:A").Find(sB.Col1.cell(2,1), , , xlWhole)
if not rng1 is Nothing then
' STEP-2 : Filter out all rows with matching MasterID from the Parent set
sA.Range("A1:Q5000").AutoFilter( _
Field:=13, _
Criteria1:=sB.Col1.cell(2,1) , _
Visibledropdown:=False)
set rng2 = sA.autofilter.range
'set rng2 = sA.autofilter.range.specialcells(xlVisible)
' Get the number of rows filtered
intFilteredRowsCount = rng2.Columns(13).SpecialCells(xlVisible).Count
if not rng2 is nothing then
' STEP-3 : Find the ChildID in the filtered rows of Parent set.
Set rng3 = rng2.Range("A1:A" & intFilteredRowsCount ).Find ( _
sB.Col3.Cells(2,3), _
, _
, _
xlWhole,
xlByColumns, xlNext)
end if
end if
I expected the "rng2" to have cells/rows only that met the filter
criterion, but though the worksheet sA displays only the filtered rows, the
find function in STEP-3 is searching the entire worksheet as if there was no
filter.
Also when I debug.print the value of rng2.cells(2,1) I would expect the
value of the first cell in the first row in set of of filtered rows, but
instead I get the value of the cell in column 1 of row 2 when the sheet was
un-filtered.
I appologise for the long body of this post. I hope I have been clear in
stating my problem -
1. Why is the rng2 visibly filtered, but not accessible as filtered set?
2. Why does the value of rng2.cell(R, C) correspond to the value of R, C
used range of the worksheet despite the fact that rng2 represents a range
which should hold a set of filtered rows?
3. How should I access the set of resulting data after I apply the
autofilter method on a range?
Your help would be deeply appreciated. Thanks, Rajesh