[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

Code ignoring filtered (visible) tasks.

Finius Eetch

12/16/2006 7:03:00 PM

mycat is a string in my I column or column 9 (defined as MyCategoryCol)

Export_Info(mycat as string)
{
....misc variables.


' Apply my filter.
Selection.AutoFilter Field:=MyCategoryCol, Criteria1:=mycat, Operator:=xlAnd

' Count the number of filtered tasks.
column_counter = Application.WorksheetFunction.Subtotal(3, Range("I:I"))


For i = 1 To column_counter



' The QUESTION
buff = Selection.Cells(i, MyQuestionCol)
ConBuff = ""
For j = 1 To Len(buff)
If InfFont(Asc(Mid(buff, j, 1))) = 255 Then
MsgBox "Unrecognised characters in Question at line " & i &
", character # " & j & " (" & Chr(InfFont(Asc(Mid(buff, j, 1)))) & "), table
entry " & Asc(Mid(buff, j, 1))
ConBuff = ConBuff + "?"
Else
ConBuff = ConBuff + Chr(InfFont(Asc(Mid(buff, j, 1))))
End If
Next j
Print #hfile, ConBuff

next i

.... many more sections of data, but they are all basically the same as this.

}


THE PROBLEM:
When I go through this loop, i always get the cells from the top, even ones
that should be filtered. I don't know why this happens. When I filter by
"Kids" for example, the 217 rows that have info about the "kids" even though
the actual row is at like 21,000 - I start getting entries from row 1, which
is usually a different column like "History"
2 Answers

Finius Eetch

12/16/2006 8:34:00 PM

0

To clarify the problem more, since I just re-read this.

In Column "I" I have the various groups.
"Kids","Adults","Babies","Grandparents", etc.

I filter based on those groups to find how many records each has.
"Kids" returns 217, Adults returns 7,350, Babies 3,750, GrandParents 500, etc.

The first entry, with no filters applied of 'KIDS' is down around the
21,000th entry. When filtered - I see the 217 rows. I then assume I can
cycle start at row 1 and start outputing the data. However, I don't get the
first row starting at the filtered KIDS, I get the first row of ALL the
entries as though they were unfiltered.

Any ideas how to adjust the top part of the code I posted so I can continue
through the rest of my code as shown?
Thanks,
Finius Eetch




"Finius Eetch" wrote:

> mycat is a string in my I column or column 9 (defined as MyCategoryCol)
>
> Export_Info(mycat as string)
> {
> ...misc variables.
>
>
> ' Apply my filter.
> Selection.AutoFilter Field:=MyCategoryCol, Criteria1:=mycat, Operator:=xlAnd
>
> ' Count the number of filtered tasks.
> column_counter = Application.WorksheetFunction.Subtotal(3, Range("I:I"))
>
>
> For i = 1 To column_counter
>
>
>
> ' The QUESTION
> buff = Selection.Cells(i, MyQuestionCol)
> ConBuff = ""
> For j = 1 To Len(buff)
> If InfFont(Asc(Mid(buff, j, 1))) = 255 Then
> MsgBox "Unrecognised characters in Question at line " & i &
> ", character # " & j & " (" & Chr(InfFont(Asc(Mid(buff, j, 1)))) & "), table
> entry " & Asc(Mid(buff, j, 1))
> ConBuff = ConBuff + "?"
> Else
> ConBuff = ConBuff + Chr(InfFont(Asc(Mid(buff, j, 1))))
> End If
> Next j
> Print #hfile, ConBuff
>
> next i
>
> ... many more sections of data, but they are all basically the same as this.
>
> }
>
>
> THE PROBLEM:
> When I go through this loop, i always get the cells from the top, even ones
> that should be filtered. I don't know why this happens. When I filter by
> "Kids" for example, the 217 rows that have info about the "kids" even though
> the actual row is at like 21,000 - I start getting entries from row 1, which
> is usually a different column like "History"

Mike Fogleman

12/17/2006 2:55:00 PM

0

I am not sure you want to use column_counter on your filtered list because
your loop is going from row to row. If column_counter =100 then your loop
will evaluate rows(1:100). Your last filtered item may be on row 1900! It
may be best to loop the entire list and check each row's Hidden property.

For i = 1 to LastRow
If Rows(i).Hidden = False Then
'Do your code
Else
End If
Next i

Mike F
"Finius Eetch" <FiniusEetch@discussions.microsoft.com> wrote in message
news:80617A12-E1B3-4A44-BFF4-F0B31A6B0356@microsoft.com...
> mycat is a string in my I column or column 9 (defined as MyCategoryCol)
>
> Export_Info(mycat as string)
> {
> ...misc variables.
>
>
> ' Apply my filter.
> Selection.AutoFilter Field:=MyCategoryCol, Criteria1:=mycat,
> Operator:=xlAnd
>
> ' Count the number of filtered tasks.
> column_counter = Application.WorksheetFunction.Subtotal(3, Range("I:I"))
>
>
> For i = 1 To column_counter
>
>
>
> ' The QUESTION
> buff = Selection.Cells(i, MyQuestionCol)
> ConBuff = ""
> For j = 1 To Len(buff)
> If InfFont(Asc(Mid(buff, j, 1))) = 255 Then
> MsgBox "Unrecognised characters in Question at line " & i &
> ", character # " & j & " (" & Chr(InfFont(Asc(Mid(buff, j, 1)))) & "),
> table
> entry " & Asc(Mid(buff, j, 1))
> ConBuff = ConBuff + "?"
> Else
> ConBuff = ConBuff + Chr(InfFont(Asc(Mid(buff, j, 1))))
> End If
> Next j
> Print #hfile, ConBuff
>
> next i
>
> ... many more sections of data, but they are all basically the same as
> this.
>
> }
>
>
> THE PROBLEM:
> When I go through this loop, i always get the cells from the top, even
> ones
> that should be filtered. I don't know why this happens. When I filter by
> "Kids" for example, the 217 rows that have info about the "kids" even
> though
> the actual row is at like 21,000 - I start getting entries from row 1,
> which
> is usually a different column like "History"