Ultimate Google Sheets VLOOKUP Tutorial Series

The ultimate series covering everything you need to know about Google Sheets VLOOKUP function. Most of it applies to Microsoft Excel as well. Make sure you watch the whole series if you want in-depth understanding of VLOOKUP function and everything related. Other functions included: INDEX, MATCH, IFERROR, UNIQUE, QUERY & more.

Excel VBA – Set Column Width Exactly the Same in Other Workbooks – How to Code Examples

This Excel macro is useful if you have one or more Excel files where you want to set the column sizes exactly the same as the other workbook.

This script will take the current active (selected) workbook (Excel File) as a reference, get all the column widths from the active sheet (selected tab) and apply exactly the same widths to the active sheets (selected tabs) on all the other open workbooks (Excel files).

This VBA code will only affect active sheets.

Don’t forget to like and share if you found the code useful!

 

 

Posted by Excel Instructor:

http://www.chicagocomputerclasses.com/excel-classes/

Excel VBA – Combine Many Tabs to One Master Worksheet – How to Code Examples

Suppose you have an Excel file with multiple tabs in the following format and you need to combine them to a single master sheet. This can either be a very long and boring copy/paste work or you can speed up the process by using the VBA code further down on this page.

A B C
1 Date Account Amount
2 1/1/2016 484194 $18,006.00
3 1/5/2016 497074 $37,865.00
4 1/9/2016 321592 $19,426.00
5 1/13/2016 784416 $37,348.00
6 1/17/2016 904154 $3,394.00
7 1/21/2016 936320 $25,869.00
8 1/25/2016 596282 $46,453.00
9 1/29/2016 257233 $8,262.00
10 2/2/2016 640688 $20,651.00
11 2/6/2016 169251 $2,652.00
12 2/10/2016 204842 $19,168.00
13 2/14/2016 403114 $38,061.00
14 2/18/2016 932651 $30,586.00
Tab1

 

Don’t forget to like and share if you found the code useful!

 

 

Posted by Excel Instructor:

http://www.chicagocomputerclasses.com/excel-classes/

VBA – Run a PowerPoint Macro using Excel VBA – How to Code Examples

Below is a demonstration of using PowerPoint.Application to open a PowerPoint file and then run a macro that is stored within the PowerPoint file.

  1. Make sure you modify “fullpath\p1.pptm” and point it to the full path-filename to your PowerPoint file.
  2. Modify the macro name. Macro name must be full macro name with file, module and macro name in your PowerPoint file in order for this script to work. Use the following format “p1.pptm!Module1.name”

 

Posted by Excel Instructor:

http://www.chicagocomputerclasses.com/excel-classes/

VBA – Display a File Open Dialog and Open the File – Excel functions

Below is a demonstration of using Application.FileDialog to give the user an option to select a file and then open it. The code is well commented and should be self explanatory.

The full file path will be stored in fullpath variable, which is used later in the code to open the file after making sure there was an Excel file selected.

 

Posted by Excel Instructor:

http://www.chicagocomputerclasses.com/excel-classes/

VBA – Display a File Open Dialog Box For the User to Select a File – Excel functions

Below is a demonstration of using Application.FileDialog to give the user an option to select a file. The code is well commented and should be self explanatory.

The full file path will be stored in fullpath variable, which can be used later in the code.

An example using the code to prompt the user to select an Excel file an open it in Excel can be found here http://www.chicagocomputerclasses.com/excel-vba-display-a-file-open-dialog-and-open-the-file-excel-functions/

 

Posted by Excel Instructor:

http://www.chicagocomputerclasses.com/excel-classes/

Excel VBA Function to SUM Unique Numbers Only – Unique SUMIF Function

First add this code to your VBA Function

 

 

Then use it in your worksheet like this

A B C D E
1 CA 11 CA =SUMIFUNIQUENUMS($B$1:$B$7,$A$1:$A$7,D1)
2 NY 14 NY 29
3 NY 15 IN 0
4 CA 11
5 CA 11
6 CA 14
7 CA 11
Sheet1

 

Posted by Excel Instructor:
http://www.chicagocomputerclasses.com/excel-classes/

VBA – Check Regular & Overtime Hours – Excel functions

Below are 2 Excel VBA UDFs to check and divide regular and overtime hours based on 40 hour week, Monday through Friday Payroll.

Date Hours Worked Regular Overtime
12/15/15 14 14 0
12/16/15 9 9 0
12/17/15 11 11 0
12/18/15 9 6 3
12/19/15 7 0 7
12/20/15 8 0 8
12/21/15 5 5 0
12/22/15 2 2 0
12/23/15 1 1 0
12/24/15 1 1 0
12/25/15 6 6 0
12/26/15 7 7 0
12/27/15 14 14 0
12/28/15 9 9 0
12/29/15 9 9 0
12/30/15 9 9 0
12/31/15 9 9 0
1/1/16 8 4 4
1/2/16 9 0 9
1/3/16 13 0 13
1/4/16 15 15 0
1/5/16 10 10 0

Posted by Excel Instructor:
http://www.chicagocomputerclasses.com/excel-classes/