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