## VBA – Get All Numbers in a Text String & Incresae by One – Excel Macro

The following procedure will take any numbers within text and increase them by 1. For Example: For20Example51Text will transform into For21Example52Text   Sub UpNumbersByOne() cell = “YOUR16STRING1HERE10” Dim v As Variant endresult = “” With CreateObject(“VBScript.RegExp”) .Pattern = “(\d+|\D+)” .Global = True v = Split(Mid(.Replace(cell, “|\$1”), 2), “|”) For Each num In v If IsNumeric(num) = […]

## VBA – Get First Number & Then Text & Following Number – Excel Macro

This Excel Macro will transform the text data placed in column A and Break it down into 3 Columns. Data Format Required: 15654 Text goes Here 16 2 6 1595 1 Different Text 1 6 number (space) text of any lenght (space) number (space) other characters Output will return: First column: full first number Second […]

## Libraries in Chicago

List of Chicago Libraries with address and phone number. NAME Address PHONE Albany Park 3401 W. Foster Avenue, Chicago, IL 60625 (773) 539-5450 Altgeld 13281 S. Corliss Avenue, Chicago, IL 60827 (312) 747-3270 Archer Heights 5055 S. Archer Avenue, Chicago, IL 60632 (312) 747-9241 Austin 5615 W. Race Avenue, Chicago, IL 60644 (312) 746-5038 Austin-Irving […]

## VBA – Delete Every Other Row – Odd or Even – Excel Macro

This Excel Macro will go through all the cells in column A, find the last cell that has data in it, and then delete every other row in that data range. To Delete Odd Rows Sub Delete_Odd() Application.ScreenUpdating = False lr = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row If lr Mod 2 = 0 Then lr = lr – […]

## VBA – Letter Count for Each Alphabet Character – Excel Macro

This Excel Macro will go through all the cells in column A and output the count for each letter in column B. This is for English alphabet only and it will skip all the other characters and spaces.   Sub LetterCount() Dim ws As Worksheet Set ws = ActiveSheet lastrow = Cells(Rows.Count, 1).End(xlUp).Row rg = […]

## Excel VBA – Export Each Worksheet to a Separate PDF – Macro

If you need to Export Each sheet to an individual .pdf file this Macro will do it for you. It will go through all the sheets in your Workbook and save each one to a separate PDF file using the worksheet name as file name.   Sub ExportToPDFs() ‘ PDF Export Macro ‘ Change C:\Exports\ […]

## Excel – Age Calculation – How to Get Age from Date of Birth (DOB)

The easiest way to calculate person’s age from using their birthday in Excel is by utilizing undocumented DATEDIF function. DATEDIF is a built-in Excel function, however, unlike all the other built-in functions it will not auto-populate, show up in auto-complete or give tooltips on the function. So how do you use DATEDIF function? The syntax for the function is […]

## Average City of Chicago Income Per Capita – Broken Down by Neighborhoods

This data is compiled using public dataset released by the City of Chicago Date Created 10/30/2014   Average of PER CAPITA INCOME \$25,597.00 COMMUNITY AREA NAME Average Near North Side \$88,669.00 Lincoln Park \$71,551.00 Loop \$65,526.00 Lake View \$60,058.00 Near South Side \$59,077.00 North Center \$57,123.00 Near West Side \$44,689.00 Forest Glen \$44,164.00 West Town \$43,198.00 […]

## Average City of Chicago Employee Salary – What Do City Employees Really Make?

This data is compiled using public dataset released by the City of Chicago Date Created 10/30/2014   Average of Employee Annual Salary  \$75,559.11 by Department DoIT \$94,083.53 BUILDINGS \$91,814.51 FIRE \$88,247.96 MAYOR’S OFFICE \$86,765.19 HUMAN RELATIONS \$85,095.33 BUDGET & MGMT \$84,022.27 IPRA \$82,412.05 BOARD OF ETHICS \$82,334.67 WATER MGMNT \$82,287.46 COMMUNITY DEVELOPMENT \$81,502.14 GENERAL SERVICES \$80,859.86 […]

## Add a New WorkSheet in Excel – VBA Macro Code

This VBA code will add a new worksheet in your Excel project. Sub AddNewWorksheet() Dim NewWorkSheet As Worksheet Set NewWorkSheet = Sheets.Add End Sub Posted by Excel Instructor: http://www.chicagocomputerclasses.com/excel-classes/