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) = True Then
        num = num + 1
        End If
        endresult = endresult & num
        Next num
End With

Debug.Print endresult

End Sub

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

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:

  1. First column: full first number
  2. Second column: full text string in the middle of surrounded numbers
  3. Third Column: first full number after text
Sub BreakTextNum()
lr = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    For Each cell In Range("a1:a" & lr)

    'grab teh number
    num = Left(cell, InStr(cell, " ") - 1)

    'grab teh text
        For i = 1 To Len(cell)
            Dim currentCharacter As String
            currentCharacter = Mid(cell, InStr(cell, " ") + i, 1)
            If IsNumeric(currentCharacter) = True Then
                GetPositionOfFirstNumericCharacter = i
                Exit For
            End If
        Next i

    txt = Mid(cell, InStr(cell, " ") + 1, i - 2)
    'grab teh after number
    lasti = i + InStr(cell, " ") - 2
    anum = Mid(cell, lasti + 2, InStr(lasti + 2, cell, " ") - lasti - 2)

    Cells(cell.Row, 2) = num
    Cells(cell.Row, 3) = txt
    Cells(cell.Row, 4) = anum

    Next cell

End Sub

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

Copy WordPress Pages and Posts

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 6100 W. Irving Park Road, Chicago, IL 60634 (312) 744-6222
Avalon 8148 S. Stony Island Avenue , Chicago, IL 60617 (312) 747-5234
Back of the Yards 2111 W. 47th Street, Chicago, IL 60609 (312) 747-9595
Beverly 1962 W. 95th Street, Chicago, IL 60643 (312) 747-9673
Bezazian 1226 W. Ainslie Street, Chicago, IL 60640 (312) 744-0019
Blackstone 4904 S. Lake Park Avenue, Chicago, IL 60615 (312) 747-0511
Brainerd 1350 W. 89th Street, Chicago, IL 60620 (312) 747-6291
Brighton Park 4314 S. Archer Avenue, Chicago, IL 60632 (312) 747-0666
Bucktown-Wicker Park 1701 N. Milwaukee Avenue, Chicago, IL 60647 (312) 744-6022
Budlong Woods 5630 N. Lincoln Avenue, Chicago, IL 60659 (312) 742-9590
Canaryville 642 W. 43rd Street, Chicago, IL 60609 (312) 747-0644
Chicago Bee 3647 S. State Street, Chicago, IL 60609 (312) 747-6872
Chicago Lawn 6120 S. Kedzie Avenue , Chicago, IL 60629 (312) 747-0639
Chinatown 2353 S. Wentworth Avenue, Chicago, IL 60616 (312) 747-8013
Clearing 6423 W. 63rd Place, Chicago, IL 60638 (312) 747-5657
Coleman 731 E. 63rd Street, Chicago, IL 60637 (312) 747-7760
Daley, Richard J.-Bridgeport 3400 S. Halsted Street, Chicago, IL 60608 (312) 747-8990
Daley, Richard M.-W Humboldt 733 N. Kedzie Avenue, Chicago, IL 60612 (312) 743-0555
Douglass 3353 W. 13th Street, Chicago, IL 60623 (312) 747-3725
Dunning 7455 W. Cornelia Avenue, Chicago, IL 60634 (312) 743-0480
Edgebrook 5331 W. Devon Avenue, Chicago, IL 60646 (312) 744-8313
Edgewater 6000 N. Broadway Street, Chicago, IL 60660 (312) 744-0718
Gage Park 2807 W. 55th Street, Chicago, IL 60632 (312) 747-0032
Galewood-Mont Clare 6871 W. Belden Avenue, Chicago, IL 60707 (312) 746-0165
Garfield Ridge 6348 S. Archer Avenue, Chicago, IL 60638 (312) 747-6094
Greater Grand Crossing 1000 East 73rd Street, Chicago, IL 60619 (312) 745-1608
Hall 4801 S. Michigan Avenue, Chicago, IL 60615 (312) 747-2541
Harold Washington-HWLC 400 S. State Street, Chicago, IL 60605 (312) 747-4300
Hegewisch 3048 E. 130th Street, Chicago, IL 60633 (312) 747-0046
Humboldt Park 1605 N. Troy Street, Chicago, IL 60647 (312) 744-2244
Independence 3548 W. Irving Park Road, Chicago, IL 60618 (312) 744-0900
Jefferson Park 5363 W. Lawrence Avenue, Chicago, IL 60630 (312) 744-1998
Jeffery Manor 2401 E. 100th Street, Chicago, IL 60617 (312) 747-6479
Kelly 6151 S. Normal Boulevard, Chicago, IL 60621 (312) 747-8418
King 3436 S. King Drive, Chicago, IL 60616 (312) 747-7543
Legler 115 S. Pulaski Road, Chicago, IL 60624 (312) 746-7730
Lincoln Belmont 1659 W. Melrose Street, Chicago, IL 60657 (312) 744-0166
Lincoln Park 1150 W. Fullerton Avenue, Chicago, IL 60614 (312) 744-1926
Little Village 2311 S. Kedzie Avenue, Chicago, IL 60623 (312) 745-1862
Logan Square 3030 W. Fullerton Avenue, Chicago, IL 60647 (312) 744-5295
Lozano 1805 S. Loomis Street, Chicago, IL 60608 (312) 746-4329
Manning 6 S. Hoyne Avenue, Chicago, IL 60612 (312) 746-6800
Mayfair 4400 W. Lawrence Avenue, Chicago, IL 60630 (312) 744-1254
McKinley Park 1915 W. 35th Street, Chicago, IL 60609 (312) 747-6082
Merlo 644 W. Belmont Avenue, Chicago, IL 60657 (312) 744-1139
Mount Greenwood 11010 S. Kedzie Avenue, Chicago, IL 60655 (312) 747-2805
Near North 310 W. Division Street, Chicago, IL 60610 (312) 744-0991
North Austin 5724 W. North Avenue, Chicago, IL 60639 (312) 746-4233
North Pulaski 4300 W. North Avenue, Chicago, IL 60639 (312) 744-9573
Northtown 6435 N. California Avenue, Chicago, IL 60645 (312) 744-2292
Oriole Park 7454 W. Balmoral Avenue, Chicago, IL 60656 (312) 744-1965
Portage-Cragin 5108 W. Belmont Avenue, Chicago, IL 60641 (312) 744-0152
Pullman 11001 S. Indiana Avenue, Chicago, IL 60628 (312) 747-2033
Roden 6083 N. Northwest Highway, Chicago, IL 60631 (312) 744-1478
Rogers Park 6907 N. Clark Street, Chicago, IL 60626 (312) 744-0156
Roosevelt 1101 W. Taylor Street, Chicago, IL 60607 (312) 746-5656
Scottsdale 4101 W. 79th Street, Chicago, IL 60652 (312) 747-0193
Sherman Park 5440 S. Racine Avenue, Chicago, IL 60609 (312) 747-0477
South Chicago 9055 S. Houston Avenue, Chicago, IL 60617 (312) 747-8065
South Shore 2505 E. 73rd Street, Chicago, IL 60649 (312) 747-5281
Sulzer Regional 4455 N. Lincoln Avenue, Chicago, IL 60625 (312) 744-7616
Thurgood Marshall 7506 S. Racine Avenue, Chicago, IL 60620 (312) 747-5927
Toman 2708 S. Pulaski Road, Chicago, IL 60623 (312) 745-1660
Uptown 929 W. Buena Avenue, Chicago, IL 60613 (312) 744-8400
Vodak-East Side 3710 E. 106th Street, Chicago, IL 60617 (312) 747-5500
Walker 11071 S. Hoyne Avenue, Chicago, IL 60643 (312) 747-1920
Water Works 163 E. Pearson Street, Chicago, IL 60611 (312) 742-8811
West Belmont 3104 N. Narragansett Avenue, Chicago, IL 60634 (312) 746-5142
West Chicago Avenue 4856 W. Chicago Avenue, Chicago, IL 60651 (312) 743-0260
West Englewood 1745 W. 63rd Street, Chicago, IL 60636 (312) 747-3481
West Lawn 4020 W. 63rd Street, Chicago, IL 60629 (312) 747-7381
West Pullman 830 W. 119th Street, Chicago, IL 60643 (312) 747-1425
West Town 1625 W. Chicago Avenue, Chicago, IL 60622 (312) 743-0450
Whitney M. Young, Jr. 7901 S. King Drive, Chicago, IL 60619 (312) 747-0039
Woodson Regional 9525 S. Halsted Street, Chicago, IL 60628 (312) 747-6900
Wrightwood-Ashburn 8530 S. Kedzie Avenue, Chicago, IL 60652 (312) 747-2696

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 - 1
End If

For i = lr To 1 Step -2
    Rows(i & ":" & i).Delete Shift:=xlUp    
Next i

 Application.ScreenUpdating = True

End Sub

To Delete Even Rows

Sub Delete_Even()

 Application.ScreenUpdating = False
lr = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

If lr Mod 2 = 0 Then
Else
lr = lr - 1
End If

For i = lr To 1 Step -2
    Rows(i & ":" & i).Delete Shift:=xlUp    
Next i

  Application.ScreenUpdating = True

End Sub

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

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 = Range("a1:a" & lastrow)
i = 1

For c = 97 To 122

cnt = 0

    For Each cell In rg

    lth = Len(cell)
        For lt = 1 To lth

            chktext = Mid(cell, lt, 1)

              If chktext = Chr(c) Then

                cnt = cnt + 1
            End If

        Next lt

    Next cell

    If cnt > 0 Then
        ws.Cells(i, 2) = Chr(c) & " = " & cnt
        i = i + 1
    End If

Next c

End Sub

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

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\ to your folder path where you need the diles saved
' Save Each Worksheet to a separate PDF file.

Dim ws As Worksheet

For Each ws In Worksheets
ws.Select
nm = ws.Name

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:="C:\Exports\" & nm & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False

Next ws

End Sub

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

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 as follows:

DATEDIF([start-date],[end-date],[return type])

To calculate person’s age using datedif function you can use the following formula:

Assumptions:

  • A1  –  the cell where you have person’s birthrate

Enter the formula where you want to output the age:

=DATEDIF(A1,TODAY(),”Y”)

If you do not want to use the dynamic TODAY() function you may reference to a call with a date on which you would like to calculate the age.

Assumptions:

  • A1  –  the cell where you have person’s birthrate
  • B1 – the date when you want to calculate teh age

Enter the formula where you want to output the age:

=DATEDIF(A1,B1,”Y”)

 

Copy WordPress Pages and Posts

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
Edison Park $40,959.00
Beverly $39,523.00
Hyde Park $39,056.00
Lincoln Square $37,524.00
Kenwood $35,911.00
Uptown $35,787.00
Mount Greenwood $34,381.00
Edgewater $33,385.00
Norwood Park $32,875.00
Logan Square $31,908.00
Calumet Heights $28,887.00
CHICAGO $28,202.00
Jefferson Park $27,751.00
Irving Park $27,249.00
Morgan Park $27,149.00
North Park $26,576.00
Garfield Ridge $26,353.00
Dunning $26,282.00
O’Hare $25,828.00
Clearing $25,113.00
Avalon Park $24,454.00
Portage Park $24,336.00
Rogers Park $23,939.00
Douglas $23,791.00
Ashburn $23,482.00
Grand Boulevard $23,472.00
West Ridge $23,040.00
Bridgeport $22,694.00
Hegewisch $22,677.00
Montclaire $22,014.00
Albany Park $21,323.00
Pullman $20,588.00
Avondale $20,039.00
Washington Height $19,713.00
South Shore $19,398.00
Oakland $19,252.00
Chatham $18,881.00
Woodlawn $18,672.00
Roseland $17,949.00
Greater Grand Crossing $17,285.00
East Side $17,104.00
McKinley Park $16,954.00
West Lawn $16,907.00
South Chicago $16,579.00
West Pullman $16,563.00
Lower West Side $16,444.00
Armour Square $16,148.00
Archer Heights $16,134.00
Austin $15,957.00
West Elsdon $15,754.00
Auburn Gresham $15,528.00
Belmont Cragin $15,461.00
Hermosa $15,089.00
South Deering $14,685.00
Washington Park $13,785.00
Humboldt park $13,781.00
Chicago Lawn $13,231.00
Brighton Park $13,089.00
East Garfield Park $12,961.00
New City $12,765.00
Burnside $12,515.00
Gage Park $12,171.00
North Lawndale $12,034.00
Englewood $11,888.00
West Englewood $11,317.00
West Garfield Park $10,934.00
Fuller Park $10,432.00
South Lawndale $10,402.00
Riverdale $8,201.00

Provided by http://www.chicagocomputerclasses.com/

Copy WordPress Pages and Posts

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
TREASURER $80,614.71
TRANSPORTN $79,869.23
INSPECTOR GEN $79,786.04
POLICE BOARD $79,068.00
PROCUREMENT $77,989.61
HEALTH $77,601.17
CULTURAL AFFAIRS $77,374.00
POLICE $76,925.82
DISABILITIES $73,565.24
BUSINESS AFFAIRS $72,114.07
ADMIN HEARNG $71,112.90
LAW $70,746.43
HUMAN RESOURCES $70,678.85
AVIATION $70,665.27
STREETS & SAN $70,260.79
FINANCE $68,608.02
LICENSE APPL COMM $65,436.00
CITY CLERK $57,845.33
CITY COUNCIL $56,850.31
BOARD OF ELECTION $55,953.41
OEMC $53,791.29
ANIMAL CONTRL $53,674.78
PUBLIC LIBRARY $51,374.80
FAMILY & SUPPORT $38,635.30

Provided by http://www.chicagocomputerclasses.com/

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