I have been using Excel’s native Autocorrect feature for better productivity for a long time which is code for helping me remember long or complex formula shortcuts mainly and other stuff. Recently I decided to up my game on using this really great Excel feature more effectively. This is what I did. 1) Firstly, I …
Following on from my recent post of Fix duplicated Conditional Formatting Rules , I mentioned Debra Dalgaleish’s website page had some great VBA to fix duplicated Conditional Formatting rules. I tweeked this code just a bit so that all you have to do is select anywhere in any Table in the active worksheet and it …
Macro to fix Duplicated Conditional Formatting rules in Tables Read More »
I recently worked on a project that had three distinct and large datasets for different geographical areas in three workbooks. Starting with the first workbook and data set in a Table in its own worksheet, I created a worksheet of seven Pivot Tables to provide seven Pivot Charts needed in their own sheet. This could …
Anyone who has used VBA to create formulas in cells knows it can be a little tedious and tricky to get the syntax right for Worksheet formulas. Tricky because string quotes need to be doubled for VBA and this is tedious because unlike a worksheet formula which gives you instant feedback, entering via VBA can …
Tip for creating syntax in VBA for Worksheet formulas Read More »
Update 11/08/21 Well two interesting recent developments have occurred regarding this post on VBA code to check range for specific characters. Microsoft MVP Rick Rothstein kindly contacted me about much more elegant code than I have below, trying to assign 6 table columns to a range variable so the macro would operate on selected table …
This post is about a common business problem of grabbing messy data from a transactional document and updating a data set to collect history. So in this post we will use VBA arrays to gather messy data ie consolidate it. In my previous life, I was in Commodity Export Marketing, so I am using the …
So this post is the 2nd part of my recent post on how Excel can automate data entry to your accounting system. See here for Part A. This example concerns automating entries on your bank statement you can reliably identify. In my case it was to record bank transfers from my cheque account to an interest …
This is about macro I have used quite often in the last few years that achieves two things: a) toggles to hide or unhide columns set by the user and b) the macro button changes in font colour and text wording to confirm to the user the change made. In range A1:J1 there are labels …
I thought I would blog some handy personal macros I use day to day. This is the first and I will do others. This one simply is a macro to select all formulas (so I called it SelectAllFormulas) on the worksheet using the SpecialCells method. Nothing hard in this and I used the macro recorder …
Further to my recent post this is one of the Business Model Renovation macros used in this project what I call one of my ‘quick and dirty’ but handy and versatile macros. It has been written this way because as I made changes to formulas in the templates I would just wanted to copy these …