HLOOKUP in Excel – With Examples

HLOOKUP function in Excel is a sibling of the VLOOKUP function. The H in the HLOOKUP stands for “Horizontal” and hence it is often called Horizontal Lookup.HLOOKUP is a very useful function for creating horizontal lookups, but as most of the tables that we deal with are vertical hence this function is not very popular.The task of the … [Read more...]

VBA On Error Statement – Handling Errors in Excel Macros

While writing Excel Macros we generally put more focus on the coding part and getting the desired result but during this process, we forget an important thing i.e. Error handling. Error handling is an important part of every code and VBA On Error Statement is an easy way for handling unexpected exceptions in Excel Macros.A well-written macro is … [Read more...]

VLOOKUP In VBA – With Examples

In my earlier post, I had written about VLOOKUP in Excel. It was a massive post of around 2500 words, it explains most of the things about the vertical lookup function in excel. Today’s post is an extension to that post and here we will understand how to apply a VLOOKUP in VBA.If you haven’t read that post then I would strongly recommend you … [Read more...]

VBA MsgBox – How to use

In VBA, the MsgBox function is used for displaying a dialog box with a predefined message. It returns an integer value based on the button clicked by the user, this helps to keep a track of the option selected by the user.VBA Msgbox can be mainly used for the below three reasons: For displaying a message to the end-user. For graceful … [Read more...]

Sum Cells based on Background Color

Few weeks back, one of my regular visitors dropped me an email describing an issue that he was facing. His task was to add the contents of certain cells based on their background colors.As we all know, Excel by default has no formula or feature to calculate such a thing. So, in this post I will share few methods that will help you to achieve … [Read more...]

VBA DIR Function – How to Use in Excel

DIR is a very special function in VBA, its job is to return a string representing the name of a file, directory, or archive that matches a specified pattern. DIR function only returns the first file name or folder name from a location that matches the specified attributes.To fetch other file names or folder names from that location that match … [Read more...]

VBA Split Function – How to Use

VBA, just like any other language gives you a set of functions to perform various operations on strings. And VBA Split is one of those string functions. Microsoft introduced the Split function with VBA Version 6 (in Office 2000).As the name suggests, the job of a Split statement is to break, split, or divide a string based on a particular … [Read more...]

VBA Loops – For, For Each, Do While and Do Until Loops

Loops are used in VBA for repeating a set of statements multiple times. Loops form a very important part of any programming language and VBA is no exception. There are different types of loops that can be used in VBA. These are as follows:1. VBA For Loop2. VBA For Each Loop3. VBA Do While Loop4. VBA Do Until LoopIn this post, I will … [Read more...]

Privacy Policy | Disclaimer | About