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 […]
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. Make sure you modify “fullpath\p1.pptm” and point it to the full path-filename to your PowerPoint file. Modify the macro name. Macro name must be full macro name with file, module and macro […]
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 […]
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 […]
First add this code to your VBA Function Function SUMIFUNIQUENUMS(rng2 As Range, rng1 As Range, rng3 As Range) num = rng1.Count – 1 Dim arr1() As Variant ReDim arr1(num) i = 0 For Each r In rng1 If r.Value = rng3.Value Then arr1(i) = 1 * rng2(i + 1) Else arr1(i) = 0 End […]
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 […]
Use this VBA code to extract a unique list of items that appear inside of multi-column data. The criteria is that the data needs to appear at least once in each column. This one has user friendly interface to run on any size data and get the results wherever user picks in the worksheet. Sub […]
This VBA code will loop though all textboxes in your userform that are following the naming convention textBox[x] (ex. textBox1, textBox2, textBox3 ….. textBox255) For Each txtboxitem In Me.Controls If Left(txtboxitem.Name, 6) = “textBox” Then If txtboxitem.Text <> “” Then ‘do something Exit For End If End If Next txtboxitem Posted by Excel Instructor: http://www.chicagocomputerclasses.com/excel-classes/
This VBA code will loop though all the text files in a directory that user picks from an application box and print a list of them. This code can be modified to be used in many scenarios for any file type. Sub LoopThroughAllTextFilesInFolder() ‘Declarations Dim wb As Workbook Dim DirPath As String Dim FileName […]
This is a basic VBA code to get started with InternetExplorer.Application object and work with web browsers and data extraction. This code is written strictly for learning proposes and should be used as such. Everything is well commenced out so it should be really easy to understand. In this example we use Internet Explorer to open […]
Locations Serving: Chicago, Loop, Downtown, North, North-East, North-West and West Suburbs, including Evanston, Northbrook, Schaumburg, Hoffman Estates and Naperville IL.