Excel Vlookup second matched value or specific occurrence

Posted on March 25, 2015September 17, 2017 by Wyman W

This tutorial explains how to write a custom VBA Excel Function to vlookup second matched value or specific occurrence. You may also want to read: Case sensitive lookup Excel Lookup Function lookup multiple criteria not in the first column Excel lookup partial text using Vlookup with Wildcard Excel lookup Function lookup multiple criteria Excel Vlookup second matched value or specific occurrence In traditional Vlookup, you are allowed to vertically lookup the first value that ma...

Excel VBA custom Function last row and last column

Posted on March 12, 2015March 23, 2017 by Wyman W

This tutorial explains how to create a custom VBA Function to find the last row and last column number in Excel worksheet. You may also want to read: Excel find the last row using Worksheet formula Excel VBA custom Function last row and last column In Excel VBA, we always need to loop through each Cell to perform some actions. For Range that you already know, you can easily use For...Each Loop to loop through each Cell for a specified Range as below For Each rng in Range("A1:C10") ...

Excel Access MRound custom Function round to nearest multiple

Posted on March 8, 2015October 4, 2016 by Wyman W

This tutorial explains how to create a custom Excel Access MRound Function to simulate Excel spreadsheet MRound Function round to nearest multiple Excel MRound Function Excel Round Function is used to round to nearest digit, while Excel MRound Function is used to round to nearest multiple. The first time I used MRound Function is to round employee bonus to nearest $100, because we want the bonus amount looks better. For example, $11040 is rounded to $11000. Access MRound Function As of Acces...

Excel VBA Custom Function check if worksheet exists

Posted on March 5, 2015December 26, 2016 by Wyman W

This tutorial explains how to use Excel VBA Custom Function to check if worksheet exists, and add new worksheet if worksheet doesn't exist Excel VBA Custom Function check if worksheet exists Sometimes we want to manipulate worksheet in a workbook but we are not sure if the worksheet exists. For example, we want to add a worksheet, if it already exists, skip the adding worksheet code. VBA code - Custom Function to check if worksheet exists There are many Functions on the internet that do the ...

Excel VBA Custom Function to Get RGB Color

Posted on February 26, 2015December 26, 2016 by Wyman W

This tutorial explains how to create an Excel VBA custom Function to get RGB Color using Interior.Color Property. Excel VBA Custom Function to Get RGB Color RGB is a VBA Property that can set RGB color. Usually Property comes in a pair, one Method is to Set Property and another is Get Property. However, RGB does not seem to have the Get Method, therefore you cannot get the RGB color directly. To get the RGB Color, we need to make use Interior.Color Property to get Interior Color code, whic...

Excel VBA custom Function Find the first colored Cell value

Posted on February 26, 2015March 21, 2017 by Wyman W

This tutorial explains how to create an Excel VBA custom Function to find the first colored Cell value in a Range using ColorIndex Property. You may also want to read Excel VBA Function sum colored Cell/ count colored Cell Find the first colored Cell value using ColorIndex Property This custom Function was originally created for a question raised in Microsoft Community. It seems that many people have been asking similar questions like finding the first non-empty Cell, finding the first ...

Access Excel custom split Function to delimit text

Posted on February 22, 2015December 26, 2016 by Wyman W

This tutorial shows how to create a custom Access Excel VBA custom Function to delimit text with the help of VBA "Split" Function instead of "Text to Columns". Delimit text in Excel (non-VBA) In Excel worksheet, there is a function called "Text to Columns" (Data > Text to Columns) to separate a text by delimiter. For example, Cell A1 contains "a,b,c" and after split by comma, A1 contains "a", B1 contains "b" and C1 contains "c". An alternative way to do that is using Left Function with...

Access Excel VBA Check Prime Number using custom Function

Posted on February 14, 2015March 8, 2015 by Wyman W

This tutorial shows a custom Access Excel VBA Function that can check prime number, the Function returns TRUE or FALSE. Access Excel VBA Check Prime Number using custom Function In this tutorial, I will show a custom Access Excel Function that can check prime number, returning TRUE or FALSE. Prime number is an integer that can only be divided by 1 or itself (without remainder). Below are some examples of prime number. 1–20 2 3 5 7 11 13 17 19 23 29 31 37 41 43 47 53 59 ...

Use VBA Excel Function to sort data in ascending order

Posted on February 11, 2015April 20, 2015 by Wyman W

This Excel tutorial shows how to use custom VBA Excel Function to sort data in ascending order instead of using Filter. You may also want to read Sort data using custom Function by assigning number to Text Sort data using Range Sort Method Sort data using Table AutoFilter Sort data using Worksheet AutoFilter Use VBA Excel Function to sort data in ascending order In this tutorial, I will show how to use VBA Excel Function to sort data instead of using Filter. This was origin...

VBA Access Excel Remove duplicates in text

Posted on February 10, 2015April 7, 2015 by Wyman W

This tutorial provides a custom Access Excel VBA Function to remove duplicates in text, parameters provide flexibility of case sensitivity and delimiter You may also want to read: Access Excel Scripting Dictionary store unique array item VBA Access Excel remove duplicates in text This tutorial is to help you to remove duplicates in text (in a Cell), but it is not about removing duplicated Cell value in a column. To remove duplicated Cell value in a column, highlight the column, navigate ...