Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Autofilter Problem

  Asked By: Gin    Date: Sep 13    Category: MS Office    Views: 607
  

mTASK:

A large XLS file is extracted form a database. Several different pivot
tables need to be generated for this raw data table. Some columns
contain several levels of a "Failure Signature" (FS). This FS needs to
be decoded into the column "QOS Category" (QOS).





PROBLEM:

An Add-in procedure "Failure Signature Pareto" (FSP) decodes the FS into
QOS and creates all the pivot tables. This works normally very well,
with one exception. The Add-in also contains many other functions. After
executing many other functions which are independent from FSP, the
decoding acts weird and gives wrong QOS values. When debugging I found
that the Autofilter is set correctly, but also writes data into hidden
(hidden due to the Autofilter!) rows. In my macro I use the constant
SpecialCells(xlCellTypeVisible) to prevent this though. This failure
occurs in Office 2002 and 2003. Can somebody help me to find the bug?






VBA CODE:



QOSC, FSOC, FSUC are integer variables of the corresponding column
number

QOSC is an empty column which is populated with the QOS Category based
on Autofilter settings for FS



Application.StatusBar = "Determine QOS Category"

Set WR = Range(Cells(2, QOSC),
Cells(ActiveSheet.UsedRange.Rows.Count, QOSC))

Rows(1).AutoFilter

On Error Resume Next



' Order entry

Rows(1).AutoFilter Field:=FSOC, Criteria1:="Order entry"

WR.SpecialCells(xlCellTypeVisible).Select

If Err = 0 Then Selection.Replace What:="", Replacement:="Logistic",
LookAt:=xlWhole

Err.Clear



' Development

Rows(1).AutoFilter Field:=FSOC, Criteria1:="Development"

Rows(1).AutoFilter Field:=FSUC, Criteria1:="Test Coverage"

WR.SpecialCells(xlCellTypeVisible).Select

If Err = 0 Then Selection.Replace What:="", Replacement:="Test",
LookAt:=xlWhole

Err.Clear



' continued with similar code for other QOS categories

Share: 

 

No Answers Found. Be the First, To Post Answer.

 
Didn't find what you were looking for? Find more on Autofilter Problem Or get search suggestion and latest updates.




Tagged: