Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

hello librebel,

many hanks for your work and effort ... but ... sadly,

the result is


A Scripting Framework error occurred while running the Basic script Standard.Module1.Calc_filterActiveCell.

Message: wrong number of parameters!

debugging attempts by stepping through with F8 result in:


BASIC runtime error. '1'

Type: com.sun.star.uno.RuntimeException

Message:

in the line 24 Dim oRange As Object : oRange = oSheet.getCellRangeByName( sRangeName )

regardless whether i replace sRangeName by a defined and named range in the sheet.

(sorry, i don't know how to format the message lines as normal text)

some thoughts:

'autofilter' would be 'nicer' than 'filter' because it gives more options for dummies - like me - to go onward from the filtered list,

the one-liner VBAProject Makros, which i 'listed' in the first post, do! work in lo with two impacting shortcomings:

  • filter arguments set for other columns are 'wiped-out' by the macro, (imagine the list is filtered for one person, when i add filtering for a special week i get this week !complete with all persons! thats much better in excel where the other filters are preserved. i'm sure it's possible in lo too, but don't now how) (i've read somewhere that the 'language' in lo and excel are the same, but the data-structures differ, maybe it's only necessary to adapt the vba 'command' to the lo data structures)

  • filtering in columns with date values results in nonsense, the hierarchical '+'-signed structure in the filter header is activated but no lines are selected ... :- (it works with dates formatted as numbers but that's difficult for humans, wrong results with DD-MM-YYYY (right line shown but nonsense in the selection box), and nonsense - none selected - with YY-MM-DD, (i'd prefer YY-MM-DD working), and! i'd prefer to get rid of this incredible unhandy selection procedure in the selection box with the 'select year, select month, select day' bullshit)

regards,

newbie-01

hello all,

can anyone help to make the macro 'modifying' the filtering criteria rather than setting a new one?

i got the macro from librebel, and found some others in the web which use autofilter, all set the filter criteria for the entire table 'from scratch' - and this way as 'not filtered' except the one column where they are working on.

excel with one simple line:

1: ActiveSheet.Range("data1").AutoFilter Field:=ActiveCell.Column, Criteria1:=ActiveCell.Text

does a 'modify', the filter criteria for all other columns stay as they are, only for the actual column the criteria is set. (or changed if there was one before (that' not! a silly idea, one could think in an already filtered column the macro would only change A to A, think of manually retyping a filtered A to B and then call the macro, you'll see all rows with B in that column, nice quick help in some cases.))

the excel behaviour is much more 'intuitive', and very flexible for daily work in large tables ... i'm sure it is! possible in LO too, but how?

three other enhancements if someone has time?:

  • the filtering for a date value formatted as YY-MM-DD should result in something reasonable,
  • the filtering for any date string should avoid the hierarchical selection-click-mania in the selector drop-down-box, just a simple list of values would do better,
  • the filtering for special formatted cells schould / could work better, LO has difficulties as soon as i format a week-number as '01'raher than '1', but thats useful for sorting whenever the number is interpreted as, or included in, a string.

reg.

newbie-01


old, solved, the macro had to be called with a parameter set:

hello librebel,

many hanks for your work and effort ... but ... sadly,

the result is


A Scripting Framework error occurred while running the Basic script Standard.Module1.Calc_filterActiveCell.

Message: wrong number of parameters!


debugging attempts by stepping through with F8 result in:


BASIC runtime error. '1'

Type: com.sun.star.uno.RuntimeException

com.sun.star.uno.RuntimeException Message:


in the line 24 Dim oRange As Object : oRange = oSheet.getCellRangeByName( sRangeName )

regardless whether i replace sRangeName by a defined and named range in the sheet.

(sorry, (solved - just lines before '---..' are formatted as bold, add a blank line to avoid; sorry, i don't know how to format the message lines as normal text)

some thoughts:

'autofilter' would be 'nicer' than 'filter' because it gives more options for dummies - like me - to go onward from the filtered list,

the one-liner VBAProject Makros, which i 'listed' in the first post, do! work in lo with two impacting shortcomings:

  • filter arguments set for other columns are 'wiped-out' by the macro, (imagine the list is filtered for one person, when i add filtering for a special week i get this week !complete with all persons! thats much better in excel where the other filters are preserved. i'm sure it's possible in lo too, but don't now how) (i've read somewhere that the 'language' in lo and excel are the same, but the data-structures differ, maybe it's only necessary to adapt the vba 'command' to the lo data structures)

  • filtering in columns with date values results in nonsense, the hierarchical '+'-signed structure in the filter header is activated but no lines are selected ... :- (it works with dates formatted as numbers but that's difficult for humans, wrong results with DD-MM-YYYY (right line shown but nonsense in the selection box), and nonsense - none selected - with YY-MM-DD, (i'd prefer YY-MM-DD working), and! i'd prefer to get rid of this incredible unhandy selection procedure in the selection box with the 'select year, select month, select day' bullshit)

regards,

newbie-01