Set up a custom button on your Excel menu and attach this Excel macro. You can then quickly convert a block of cells from debit to credit, for example. This Excel macro written in Visual Basic for Applications multiplies your block of selected cells by negative one ('-1'):

 

Sub Select_Cells_to_Multiply_by_Negative_1()

' Macro VBA code written by Lynn Preston.

' User first selects a block of cells, in an combination of rows or columns.

' Run macro and all cells are multiplied by negative one. Debits to credits!

' Dimension variables

Dim xRng, xCounter As Integer, Debit As Currency

 

Set xRng = Selection ' uses user-selected range in Excel

' Loop once for every column in the selection.

For xCounter = 1 To xRng.Cells.Count

'Debit = xRng.Cells(xCounter).Value)*(-1.00)

Debit = (xRng.Cells(xCounter).Value) * (-1#)

xRng.Cells(xCounter).Value = Debit

Next xCounter

End Sub

 

On occasion, you might receive input into Excel, such as a social security number, that contains an invisible character that you would like to eliminate. This Excel macro written in Visual Basic for Applications removes the bad FIRST character from an SSN:

 

Sub Remove_Bad_1st_character_from_SSN()

' Macro VBA code written by Lynn Preston.

' User first selects cells in single column of Excel that have bad 1st character of

' 10 digit SSN's

' Dimension variables

Dim xRng, xCounter As Integer, SSN As String

 

Set xRng = Selection ' uses user-selected range in Excel

' Loop once for every row in the selection.

For xCounter = 1 To xRng.Rows.Count

SSN = Right((xRng.Cells(xCounter).Value), 9)

xRng.Cells(xCounter).Value = SSN

Next xCounter

End Sub

 

On occasion, you might receive input into Excel, such as a social security number, that contains an invisible character that you would like to eliminate. This Excel macro written in Visual Basic for Applications removes the bad TENTH character from an SSN:

Sub Remove_Bad_10th_character_from_SSN()

' Macro VBA code written by Lynn Preston.

' User first selects cells in single column of Excel that have the 10th incorrect character of SSN's to be truncated

' Dimension variables

Dim xRng, xCounter As Integer, SSN As String

 

Set xRng = Selection ' uses user-selected range in Excel

' Loop once for every row in the selection.

For xCounter = 1 To xRng.Rows.Count

SSN = Left((xRng.Cells(xCounter).Value), 9)

xRng.Cells(xCounter).Value = SSN

Next xCounter

End Sub

 

On occasion, you might receive two columns of names in Excel which you would like to combine quickly into a single column. This Excel macro written in Visual Basic for Applications: Combines the First Column + Blank Space + Second Column and Loads into First Column:

Sub Combine_Names()

'

' Macro VBA code written by Lynn Preston.

' User first selects cells in two columns of Excel:

' First column has First Name

' Second column has Last Name

' Combine First Column + Blank + Second Column and Load into First Column

 

' Dimension variables

 

Dim xRng, xCounter, Last_name_counter As Integer

Dim Combined_Name, Blank1 As String

Blank1 = " "

Set xRng = Selection

' uses user-selected range in Excel.

'Loop once for every row in the selected columns.

For xCounter = 1 To xRng.Cells.Count

Combined_Name = Blank1

Last_name_counter = xCounter + 1

Combined_Name = RTrim(xRng.Cells(xCounter).Value) & Blank1 & RTrim(xRng.Cells(Last_name_counter).Value)

xRng.Cells(xCounter).Value = Combined_Name

xCounter = xCounter + 1

Next xCounter

End Sub

 

Your organization might have an ID to format. For example, an ID of A12345678 is output from your companywide database, yet you would like your Excel report to output a formatted ID of, A-1234-5678. This Excel macro written in Visual Basic for Applications: Formats a Company ID from A12345678 to A-1234-5678:

Sub Format_COMPANY_ID()

'

' Format_COMPANY_ID Macro

' Macro VBA code written by Lynn Preston.

' User first selects cells in single column of Excel that has the COMPANY ID numbers to be formatted.

' Dimension variables

 

Dim xRng, xCounter As Integer

Dim ID_Alpha_Char, ID_1st_4_Nbrs, ID_Last_4_Nbrs, ID_Formatted, Hyphen1, Hyphen2, No_Blanks As String

Hyphen1 = "-"

Hyphen2 = "-"

Set xRng = Selection ' uses user-selected range in Excel.

'Loop once for every row in the selected column.

For xCounter = 1 To xRng.Rows.Count

No_Blanks = LTrim(xRng.Cells(xCounter).Value)

ID_Alpha_Char = Left(No_Blanks, 1)

ID_1st_4_Nbrs = Mid(No_Blanks, 2, 4)

ID_Last_4_Nbrs = Right(No_Blanks, 4)

ID_Formatted = Split(ID_Alpha_Char + Hyphen1 + ID_1st_4_Nbrs + Hyphen2 + ID_Last_4_Nbrs)

xRng.Cells(xCounter).Value = ID_Formatted

Next xCounter

End Sub