Tag Archives: VBA

Excel: Extract a substring…starting from the end

Now there’s a whole heap of posts, forum answers and articles out there on how to find a substring in Excel by playing around with formulae. The problem that almost all of these have is that they assume you always want to start from the beginning of the string. But what if you need to find information from within the string instead? Continue reading

Excel 2003: IFERROR

For those who’ve now experienced Excel 2007 or 2010, you may well be aware that there is a lovely replacement for the good old scenario of:

 IF(ISERROR(myFormula),myOtherFormula,myFormula)

This is, of course, the IFERROR function. But for those still using 2003 you’re stuck with the above. Now we know that this works, but it can very quickly get big, bulky and nasty. And with multiple nesting formulae it’s hard to spot when or where it’s gone wrong.

So, how to deal with this? Continue reading

Excel Pivot Tables: Manipulation, Averages & More

This question about Pivot Tables came my way recently:

I have to add anywhere between 30-100 items to the values area on a series of worksheets. Is there a quick way to add these items (other than drag & drop/right-click on each one)? Once on my table, I want to display the average of the columns of data (as opposed to the default Sum). Is there an easy way to convert them to averages?

Due to security restrictions, I am unable to download software and/or custom add-ins. Continue reading

Word: Updating Autofields

MS Word is a peculiar beast and it should be remembered that, just because it comes out of the Microsoft stable, this doesn’t mean it works the same as MS Excel. There are some obscure reasons for this peculiarity but, rather than go into these in depth, I’ll limit the scope of this article to a specific question posed recently. Continue reading

Excel: Data Sort Using More Than 3 Columns

Excel was designed so that a user can sort by 3 columns, and that’s it. Why 3? Who knows, maybe the developers figured that 3 would be enough for any user of Excel and if they wanted more they’d be using a different application entirely. For whatever reason, that’s what we’re stuck with when using the Data>Sort tool provided. Continue reading

Excel: Formatting vs. Styles

This one was prompted by a problem posed on an Excel forum, for which I provided a solution. The original issue was actually to do with hiding/unhiding rows programmatically based on user selection from a drop-down, a function that required use of the Worksheet_Change event and Set Intersect, with additional code to hide/unhide…but that’s beside the point, so we’ll skip that one for now. Continue reading

Excel: Defined Names

A Defined Name is a text descriptor created by the user/developer to describe content, meaning or use of a cell or range of cells, a constant, or a formula. Once defined this can be used in place of cell addresses or other content and makes formulae much easier to understand and/or maintain.  Continue reading