So when you sandwich the variable between quotations marks like this Excel doesn’t complain: Sandwiched sort order variableĪlternatively you can put the quotation mark in its own variable: Putting quotation mark into a variableīut you cannot add them to the variable itself: Excel does not like this!īut here’s the rub, even though Excel will accept a variable as outlined above you’ll find that your range will still not sort correctly! In other words, using a variable, even if it’s correctly sandwiched between quote marks, simply will not work! Run-time error ’13’: Type mismatchĪs it turns out the CustomOrder property really wants to see those quotation marks. Logically one would assume that you could set a string variable with the sort order and assign the CustomOrder property that variable like this: Using a variable alone will not work!īut this does not work and Excel gives you the not-so-helpful Run-time error ’13’: Type mismatch error.
When Excel records a custom sort order the CustomOrder property is set with a comma separated list enclosed in quotation marks. For instance, I may either setup a table on a hidden sheet with the sort order, or may allow the user to set the sort order by placing a table on a sheet they have access to. I am often sorting using a custom order, and in my programs sometimes need that order to be dynamic.
Admittedly this is because I don’t always remember the code, but with Excel’s super cool record macro feature there is no need to!
When I need to sort a range or a table I usually use Excel’s record macro feature to get started, then tweak it to fit the parameters of the program.
0 kommentar(er)
