Criteria1:="Anzeigen"......huh?

  • Thread starter Thread starter CLR
  • Start date Start date
C

CLR

I received a workbook today to edit and it contains a situation I do not
understand. It has what appears to be a simple Autofilter macro, but when
initiated, it does something besides what it appears to be meant to do. The
code is in English, "mostly", but the comments are in what appears to me to
be German, which I don't understand. I have included the entire macro
below, with all comments in hopes someone can explain what might be
happening.

When initiated, the macro seems to "freeze" rows 1:7, (without really
freezing them), and appears to filter on column F, (not field 1 as suggested
in the code), for only numeric items, and also returns the sub header rows
the items are situated in as well as the rows which have numbers in columnF,
and also returns rows 221:228 which contain summary formulas, sort of like a
footer but not really cause there is also a real footer........and the
display is in the "Page break view"..........obviously there is much more
code going on, but I sure can't find it.......no other visible modules or
change-event code that I can see.................can there be other hidden
code that I don't know where to look? And what in the heck is an
"Anzeigen"?

'**********************************************************
' Selektion_anzeigen Makro
' Makro am 22.07.2003 von MW aufgezeigt
'
' sollte die Selektion per Makro nicht mehr funktionieren, so hat vermutlich
' jemand mit DATEN-FILTER-AUTO FILTER gearbeitet und dann die Arbeitsmappe
' gespeichert
' => auf die Spalte Q muß ein AutoFilter mit "Alle" gelegt sein
'
Sub Selektion_anzeigen()
'
' die "zerquetschte" Spalte Q enthält die Formel
' =WENN(SUMME(G9:H12)>0;"Anzeigen";"nicht Anzeigen");
' auf diese Spalte Q ist ein AutoFilter gelegt, der
' mit diesem Makro aktiviert wird
Application.Goto reference:="r1c1"
Selection.AutoFilter field:=1, Criteria1:="Anzeigen"
End Sub
'************************************************************

Any suggestions would me much appreciated.........
Vaya con Dios,
Chuck, CABGx3
 
My German is rusted up, but this is definitely German! Just about everything
is a description on what the macro is supposed to do, though. Anzeigen, I
think, means view
 
Thanks kassie.........every step helps.

....any German to English interpreters out there please?

Vaya con Dios,
Chuck, CABGx3
 
Hi Chuck

Try
http://babelfish.altavista.com/tr

Copying the first part in and asking for a translation gave
if the selection should not function by macro any longer, then probably
someone worked with DATA FILTER CAR FILTER and then the working folder
stored = on the column Q must be put an autofilter with "all"
and

column "crushed" Q contains the formula ' on this column Q is put an
autofilter, which is activated with this macro

and
Anzeigen
came up with Announcement.
 
OK, thanks Roger......I think I have a handle on how the sheet works now.
There was a hidden column M with the formula
=IF(SUM(F11:G11)>0,"Anzeigen","nicht Anzeigen") and the AutoFilter was
actually filtering on that column M, (considering it Field 1) instead of A,
(where the R1C1 code sent the cursor), or B (which contained text), or H or L
(which contained dates).......I dunno why, cause if I manually go to A1 and
do Data > Filter > Autofilter, the Autofilter applies only to A1 and B1 and
skips the rest of row 1 because C1 is empty.....it never gets to M1 which the
macro considers as Field 1 for the criteria "Anzeigen"..........strange

So, I know what the macro does now, so I can edit it......I just don't know
how in the world it does it...<g>
But anyway, I never could have got there without your help and I sure do
appreciate it.

Many thanks
Vaya con Dios,
Chuck, CABGx3
 
Thanks Ed......I think I'm over the hump with this one for now, but I'll keep
that in mind if I run in to any more......

Vaya con Dios,
Chuck, CABGx3
 
Thanks Tom, but I checked that before and only found two named ranges,
PrintArea,=Sheet1!$A$8:$L$350 and PrintTitles, =Sheet1!$1:$7.....and when I
step through the macro, the line "Application.Goto reference:="r1c1" " takes
the cursor to cell A1

But, come to find out, that the workbook has been SAVED with the Autofilter
on, and column M, the hidden column is the only column that has the
Autofilter Dropdown arrow on it.......remming out the "Application.Goto
reference:="r1c1" " line has no affect, the macro still goes ahead and
filters on column M......but if I un-set the autofilter manually, the thing
blows up for when it resets the Autofilter, it does so on A1........

This has been a very confuzing experience............I can't imagine the
purpose, as the whole thing can easily be done in a straightforward
manner.......

Anyway, thanks for your concern.

Vaya con Dios,
Chuck, CABGx3
 
Yeah, apparently this workbook has been set up by highlighting column M and
then turning the Autofilter on, then saving the workbook. This apparently
makes the macro think that " Selection.AutoFilter field:=1," is refering to
cell M1...........then hiding column M just confounded the issue. The whole
things boils down to a clandestine means of changing field:=1 to field:=13
......the goto "R1C1" just being camoflauge..........and if anybody ever
resets the Autofilter before saving the workbook, the whole thing blows up
without a trace..........

Hope this info is helpful..........it sure cost me a lot of time to learn it.

Vaya con Dios,
Chuck, CABGx3
 
Hi Chuck

I realise that you have had other postings, and that you have worked
out the answer for yourself, but I thought I would still post.

After my last posting to you, I had to go out and have been travelling
for some while in the car.
I was mulling over your problem whilst driving, and had thought to
myself that if Autofilter had been set just on column M, or any range of
cells from M onward, then Autofilter field:=1 would refer to column M as
that is the way it works - it counts from the first filter on the sheet
going across.

I didn't know whether it would work when the column was hidden though,
but a quick test back here at my screen proves that it does.

It has nothing to do with R1C!, or hiding the column before saving. It
does not have to refer to M1, it could be anywhere in column M (in this
case) that the autofilter has been applied. As you say, it all gets
blown apart if the user happens to remove Autofilter before the macro is
activated.

If one was wanting to use this as a technique, I think I would be
inclined to do something like the following

Sub Test()
Application.ScreenUpdating=False
Columns("M:M").Select
Selection.EntireColumn.Hidden = False
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
Range("M11").Select ' deliberately chose a rnage other than M1
Selection.AutoFilter
Columns("M:M").Select
Selection.EntireColumn.Hidden = True
Selection.AutoFilter field:=1, Criteria1:="Anzeigen"
Application.ScreenUpdating = True
End Sub

This would ensure the user hadn't removed the Autofilter - and, of
course, it would have been much clearer to you what was happening and
saved all those lost hours!!!!
 
Yeah, apparently this workbook has been set up by highlighting column M
then turning the Autofilter on, then saving the workbook.
Click to expand...

I doubt that is true.

If you send me the workbook, I would be glad to take a look. I don't think
the goto R1C1 is a camoflauge.

(e-mail address removed)
 
It sounds simple when you explain it that way Roger. Thanks for thinking
about me.

Vaya con Dios,
Chuck, CABGx3
 
Back
Top