Blog - Page 3 of 3 - Houston Computer Classes

VBA – Letter Count for Each Alphabet Character – Excel Macro

April 16, 2015/in Excel, Reference, Tutorial /by admin

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" &amp; 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 &gt; 0 Then
        ws.Cells(i, 2) = Chr(c) &amp; " = " &amp; cnt
        i = i + 1
    End If

Next c

End Sub

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

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.houstoncomputerclasses.com/excel-classes/

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

March 25, 2015/in Excel, Reference, Tutorial /by admin

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

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

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.houstoncomputerclasses.com/excel-classes/

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

March 17, 2015/in Excel, Reference, Tutorial /by admin

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”)

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

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

October 30, 2014/in News /by admin

This data is compiled using public dataset released by the City of Chicago
Date Created 10/30/2014

COMMUNITY AREA NAME	Average
Average of PER CAPITA INCOME	$25,597.00

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.houstoncomputerclasses.com/

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

October 30, 2014/in News /by admin

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.houstoncomputerclasses.com/

Add a New WorkSheet in Excel – VBA Macro Code

June 13, 2014/in Excel, Learn /by admin

This VBA code will add a new worksheet in your Excel project.

Sub AddNewWorksheet()

 Dim NewWorkSheet As Worksheet
 Set NewWorkSheet = Sheets.Add

End Sub

1

2

3

4

5

6

Sub AddNewWorksheet()

Dim NewWorkSheet As Worksheet

Set NewWorkSheet = Sheets.Add

End Sub

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

Convert CrossTab Table to Database List Table – Excel VBA Macro Code

June 13, 2014/in Excel, Learn, Office /by admin

If you work with databases and regular data tables you probably had a situation when someone gave you a regular Excel 2 dimensional spreadsheet that is not that useful if you need to use the data within a database and run some queries or just make PivotTables in Excel. The Macro code below will transform your CrossTab table to a regular database list table.

This script will create a new sheet and prompt you to enter a starting point for your converted data. Then it will render the data in 3 columns as a regular database data table.

Sub CrossTabToDatabase()
 Dim DataTable As Range, OutputRange As Range
 Dim RowOutput As Long
 Dim r As Long, c As Long
Dim WS As Worksheet

On Error Resume Next
 Set DataTable = ActiveCell.CurrentRegion
 If DataTable.Count = 1 Or DataTable.Rows.Count &lt; 3 Then
 MsgBox "Select a cell within the summary table", vbCritical
 Exit Sub
 End If
 DataTable.Select

 Set WS = Sheets.Add
 Set OutputRange = Application.InputBox(prompt:="Select a cell starting where you'd like to output the new datatable.", Type:=8)
' Convert the range
 RowOutput = 2
 Application.ScreenUpdating = False
 OutputRange.Range("A1:C3") = Array("Column1", "Column2", "Column3")
 For r = 2 To DataTable.Rows.Count
 For c = 2 To DataTable.Columns.Count
 OutputRange.Cells(RowOutput, 1) = DataTable.Cells(r, 1)
 OutputRange.Cells(RowOutput, 2) = DataTable.Cells(1, c)
 OutputRange.Cells(RowOutput, 3) = DataTable.Cells(r, c)
 OutputRange.Cells(RowOutput, 3).NumberFormat = DataTable.Cells(r, c).NumberFormat
 RowOutput = RowOutput + 1
 Next c
 Next r
End Sub

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

Sub CrossTabToDatabase()

Dim DataTable As Range, OutputRange As Range

Dim RowOutput As Long

Dim r As Long, c As Long

Dim WS As Worksheet

On Error Resume Next

Set DataTable = ActiveCell.CurrentRegion

If DataTable.Count = 1 Or DataTable.Rows.Count < 3 Then

MsgBox "Select a cell within the summary table", vbCritical

Exit Sub

End If

DataTable.Select

Set WS = Sheets.Add

Set OutputRange = Application.InputBox(prompt:="Select a cell starting where you'd like to output the new datatable.", Type:=8)

' Convert the range

RowOutput = 2

Application.ScreenUpdating = False

OutputRange.Range("A1:C3") = Array("Column1", "Column2", "Column3")

For r = 2 To DataTable.Rows.Count

For c = 2 To DataTable.Columns.Count

OutputRange.Cells(RowOutput, 1) = DataTable.Cells(r, 1)

OutputRange.Cells(RowOutput, 2) = DataTable.Cells(1, c)

OutputRange.Cells(RowOutput, 3) = DataTable.Cells(r, c)

OutputRange.Cells(RowOutput, 3).NumberFormat = DataTable.Cells(r, c).NumberFormat

RowOutput = RowOutput + 1

Next c

Next r

End Sub

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

Delete All Hidden Columns and Rows – VBA Excel – Code

June 12, 2014/in Excel, Learn /by admin

This VBA code can help you delete all hidden columns and rows from your excel sheet with a click of a button.

<em>Sub hiddendelete()</em>
<em>For lp = 256 To 1 Step -1 'loop through all columns</em>
<em>If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else</em>
<em>Next</em>
<em>For lp = 65536 To 1 Step -1 'loop through all rows</em>
<em>If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else</em>
<em>Next</em>
<em>End Sub</em>

1

2

3

4

5

6

7

8

Sub hiddendelete()

For lp = 256 To 1 Step -1 'loop through all columns

If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else

Next

For lp = 65536 To 1 Step -1 'loop through all rows

If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else

Next

End Sub

So how do you use DATEDIF function?

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

This data is compiled using public dataset released by the City of Chicago Date Created 10/30/2014

This data is compiled using public dataset released by the City of Chicago Date Created 10/30/2014

Locations:

This data is compiled using public dataset released by the City of Chicago
Date Created 10/30/2014

This data is compiled using public dataset released by the City of Chicago
Date Created 10/30/2014