Automate Excel

Excel Conditional Formatting

Tom — Fri, 27 Feb 2009 15:46:32 +0000

Excel has a strong and often missed feature, called Conditional Formatting, which allows you to format a cell depending on a rule [it's value]. This can be very useful for worksheets like Product Quantities and Dates. Let me show you what I mean. Lets start off with a simple chart of products quantities in the warehouse. We’ll [...]

VBA Routine to Sort Sheets

Kaps — Wed, 05 Nov 2008 18:21:49 +0000

The following routine will sort the sheets in a workbook alphabetically. The flag “Sort_Mode_Descending” can be switched between descending and ascending as required. The routine is case INSENSITIVE. Sub Sort_Sheets() Dim Sort_Mode_Descending As Boolean Dim No_of_Sheets As Integer Dim Outer_Loop As Integer Dim Inner_Loop As Integer No_of_Sheets = Sheets.Count 'Change Flag As appropriate Sort_Mode_Descending = False For Outer_Loop = 1 To No_of_Sheets [...]

VBA Routine to return Column Letter of Cell

Kaps — Tue, 04 Nov 2008 04:27:03 +0000

The standard Excel “Column” Function returns the number rather than the letter of the column e.g: Column(E4) – returns the number 5 rather than the letter E Column(AD12) returns the number 30 rather than AD. The following function returns the letter rather than the number of the column. So in the above two examples we have the letters [...]

VBA: Extract Number From String

Kaps — Mon, 03 Nov 2008 16:16:49 +0000

The following function will extract the numerical portion from a string: Function Extract_Number_from_Text(Phrase As String) As Double Dim Length_of_String As Integer Dim Current_Pos As Integer Dim Temp As String Length_of_String = Len(Phrase) Temp = "" For Current_Pos = 1 To Length_of_String If (Mid(Phrase, Current_Pos, 1) = "-") Then Temp = Temp & Mid(Phrase, Current_Pos, 1) End If If (Mid(Phrase, Current_Pos, 1) = ".") Then [...]

Find the nth Word in a String

Kaps — Fri, 31 Oct 2008 15:54:19 +0000

The following function will find the nth word in a string: Function Find_nth_word(Phrase As String, n As Integer) As String Dim Current_Pos As Long Dim Length_of_String As Integer Dim Current_Word_No As Integer Find_nth_word = "" Current_Word_No = 1 'Remove Leading Spaces Phrase = Trim(Phrase) Length_of_String = Len(Phrase) For Current_Pos = 1 To Length_of_String If (Current_Word_No = n) Then [...]

Adding and Naming Sheets to Excel Workbook

Kaps — Tue, 28 Oct 2008 20:44:44 +0000

The following code works opening a workbook. It automatically adds a new sheet and labels it with the date. It also checks to see that the sheet doesn’t already exist – to allow for the possibility of it being opened more than once a day. This code makes use of the Workbook Open Event and must [...]

Adding and Saving Workbooks

Kaps — Mon, 27 Oct 2008 18:16:57 +0000

The following code will prompt the user for the name of a workbook. It will then create and save a new workbook with the same name. Private Sub CommandButton1_Click() Dim Workbook_Name As String Dim New_Workbook As Workbook Set New_Workbook = Nothing Workbook_Name = InputBox(Prompt:="Workbook Name.", Title:="Enter the WorkBook Name :") Set New_Workbook = Workbooks.Add With New_Workbook .Activate .SaveAs Workbook_Name End With End Sub To download the .XLSM file [...]

VBA Function to Show Cell Formula

Kaps — Fri, 24 Oct 2008 12:58:32 +0000

The following UDF will return the formulae for a given cell reference: Function Show_Cell_Formulae(Cell As Range) As String Show_Cell_Formulae = "Cell " & Cell.Address & " has the formulae : ' " & Cell.Formula & " '" End Function So if we have: Where the cell B7 has the formulae : ‘=MAX(B5:G5) then we can type [...]

Matching Multiple Items

Kaps — Wed, 22 Oct 2008 03:58:59 +0000

Imagine that we have the following 2 lists each consisting of 3 columns of data: And we need to find those items that are in List 1 that are in List 2. However ALL 3 columns of data must match for items to be in both lists. One way would be to construct a comprehensive IF [...]

Convert Matrix to Vector

Kaps — Tue, 21 Oct 2008 12:09:44 +0000

This is the opposite tutorial of the Converting a Single Row Vector into a Matrix tutorial. The following function will take a range as a matrix and convert it to a single column vector: Option Explicit Function Create_Vector(Matrix_Range As Range) As Variant Dim No_of_Cols As Integer, No_Of_Rows As Integer Dim i As Integer Dim j As Integer Dim Cell No_of_Cols = Matrix_Range.Columns.Count No_Of_Rows = [...]