- Home
- Office
Excel VBA Macros : Format Text
The Story So Far… In our last post (click here to read it) we created a macro called “mySecondMacro”. This changed the 4 headings on our file to this… … and our Macro was this… If you have been following these posts you will already have a file saved as above. Or you can […]
Continue readingExcel VBA Macros : How To Change Text In A Cell
The Story So Far… Our series of posts on Excel VBA Macros began with these posts: Introduction Setting up Excel for VBA Recording your first Macro (and challenge question) Answer to challenge question Reading your first Macro If you have not already read them, I would suggest going through them now before continuing. The last […]
Continue readingExcel VBA Macros : Understanding Our First Macro
The Story So Far…. If you have been following these posts so far, you should have read Introduction Introduction 2 & Setting Up Excel For Macros Recording A Macro & Challenge Question Answer To Challenge Question You should have a file saved from the earlier posts where you recorded a macro and ran it using […]
Continue readingExcel VBA Macros : Answer To Challenge Question 1
The Challenge In the last post (click here) we recorded a macro. This macro took our basic report….. and automatically formatted the basic report to a layout that we needed….. However, when we added in an extra row and ran the macro, it didn’t give us what we might have expected….. Your challenge was […]
Continue readingExcel VBA Macros : Recording Your First Macro
This post will give you more of an introduction to Excel VBA Macros and you will be recording your first macro. After recording the macro, we will then look at the code generated and start breaking it down, this will be the start of your adventures coding macros!! The Story So Far….. The following […]
Continue readingExcel VBA Macros : Introduction
If you spend a lot of time working with Office applications such as Excel, Word, PowerPoint, etc. there is a good chance that you do repetitive and monotonous tasks, probably without thinking about it or questioning it. Over the next few posts I will talk about Excel VBA Macros and the first examples will look […]
Continue readingMaking Office work for you with VBA Macros
Don’t be a slave to your computer!! Introduction to VBA Macros You may have heard references to VBA or Macros in the past, but what are they? VBA VBA, or Visual Basic for Applications, allows users to increase and improve the functionality of some programs by making more use of menus, tool-bars and options already […]
Continue readingIFERROR
The IFERROR formula in Excel returns a value you specify if a formula results in an error, if there is no error it returns the value of the original formula. The IFERROR function is relatively new to Excel (I believe it started in Excel 2007). For a similar workaround to some VLOOKUP formula error results, […]
Continue readingVLOOKUP
The VLOOKUP is one of Excel’s most useful functions. In its most common form, it is a database function which works with lists of things in databases, or Excel worksheets. Your list of things can be pretty much anything, names and phone numbers, part numbers and details, details of your album collections, etc. A VLOOKUP […]
Continue readingWildcards in Formula
A wildcard character is a character that can be used as a substitute for another character or characters. I use wildcards in formula mostly in reference or look-up formulas like COUNTIF or SUMIF for example. The wildcards can be used to look up words beginning with or ending with criteria. Note : It can […]
Continue readingSUMPRODUCT
Continuing the series of posts about useful, but sometimes underused Excel formulas, this post will give you an introduction to SUMPRODUCT. SUMPRODUCT is one formula that pretty much combines two together. SUM : When you add two or more numbers together, the result of these is the “SUM” of them. PRODUCT : When you multiply […]
Continue readingHow to get filename in Excel
As a follow on from the last post, I was asked how to get filename in Excel using similar formulas. This can be done with the formula below. =MID(CELL(“filename”),(SEARCH(“[“,CELL(“filename”))+1),(SEARCH(“]”,CELL(“filename”))-6-(SEARCH(“[“,CELL(“filename”))))) Really simple isn’t it? I bet you were wondering how you didn’t figure it out yourself!! This combines 3 formulas, MID, CELL, and SEARCH. I’ll […]
Continue readingTHE LEN LEFT RIGHT MID FORMULAS IN EXCEL
The LEN LEFT RIGHT MID Formula are 4 Excel formula that can be really useful. They might not be used that often, but when you need them, they can save you a lot of time. I will go through each of the 4 formulas and then give examples of where they can be combined. For […]
Continue readingCOUNTIF
The COUNTIF formula is another very useful formula in Excel for analysing data and getting more information from it. If you have not already read these posts, please take a few minutes to read them now. The post on brackets and commas will show you how to break down longer formulas making them easier to […]
Continue readingHow to auto fill sequential letters in Excel
This week I was asked how to auto-fill sequential letters in Excel. What do I mean by this? If you type 1 in a cell and drag it down Excel can populate the cells with sequential numbers (2, 3, 4, 5, 6, 7, 8, etc). A user needed a list of sequential letters (a, b, […]
Continue reading