Automate Excel

You may need a workbook to always open to a specific view or location on a sheet, or possibly just change the view from a macro. You can do this by using the ScrollRow and ScrollColumn.

ScrollRow:
Used to programmatically scroll a spreadsheet vertically. This example will scroll a spreadsheet to row 5.

ActiveWindow.ScrollRow = 5

ScrollColumn:
Used to programmatically scroll a spreadsheet horizontally. This example will scroll a spreadsheet to column 5.

ActiveWindow.ScrollColumn = 5

And placing the following code in a module will always scroll a workbook’s Sheet1 to row 5 and column 5 if macros are enabled upon opening:

Sub auto_open()

    Sheet1.Activate
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollRow = 5

End Sub

This one is not very practical, especially since you need to alter each cell comment(or copy and paste), but still worth noting.

To modify a cell comments autoshape:

Insert a comment
1. Right Click a cell and select “Insert Comment”, type the text you’d like as your comment.

Edit the comments autoshape
1. Right click the cell and select “Edit Comment”
2. Click on the gray square border surrounding the comment, this takes it out of edit mode.
3. On the drawing toolbar select the word “Draw”, this should be on the left most part of the drawing toolbar for most.

4. From the popup select “Change Autoshape”, and pick the new autoshape for your comment.

A comment can be added to a formula by adding an N function. Or
=Formula+N(”YourTextHere”)

The =N() function returns the number for a given cell. For instance if 1% is in a percentage formatted cell A1, putting =N(A1) in another cell will return the number .01

However when the N function can’t convert the given information it returns 0. So to add a comment to a formula we simply add an N function to the formula, which in turn returns zero, and doesn’t alter the formula in most cases.

This picture demonstrates the use of the N function, added to an existing formula to provide a comment.

The ability to use Excel’s built in functions when programming with VBA can be a great time saver.

In this example I’ll use the Combin function. Remember Combin is used to return the maximum combinations when given the number in the pool and the number drawn. The following code demonstrates a worksheet function used in VBA to find the combinations when given a pool of 42 with a draw of 6:

Sub UseFunction()

MsgBox Application.WorksheetFunction.Combin(42, 6)

End Sub

Running this macro tells us in a message box the result of the Combin function, and we’ve successfully used a worksheet function with VBA.

There are many reasons you may want to be able to programmatically draw boxes, an automated gantt chart would be cool? Here is one way to do it.

This is the basic code to draw a box:

ActiveSheet.Shapes.AddShape _
(msoShapeFlowchartProcess, 0, 0, 48, 12.75).Select
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 11
    Selection.ShapeRange.Fill.Solid
    Selection.ShapeRange.Fill.Visible = msoTrue

It’s really not that daunting, there are only two notable things to look at; Schemecolor is obviously the color and the 0,0,48,12.75.

So what’s the 0,0,48,12.75? Simply the Left Position, Top Position, Width, and Height. You can adjust these however you would like, adjusting the first two to change the boxes position on the screen, and the latter two to change the size of the box. I used these dimensions becuase they were a good guess at making a box the same size as a cell.

And here is the code used to dynamically draw the green boxes in the above picture:

Sub DynamicBoxes()

   Dim x As Double

   'This makes horizontal boxes
   For x = 0 To 240 Step 48

    'reference to the 4 numbers left,top,width,height
 ActiveSheet.Shapes.AddShape _
(msoShapeFlowchartProcess, x, 0, 48, 12.75).Select
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 11
    Selection.ShapeRange.Fill.Solid
    Selection.ShapeRange.Fill.Visible = msoTrue

    Next x

    'This makes vertical boxes
    For x = 0 To 127.5 Step 12.75

ActiveSheet.Shapes.AddShape _
(msoShapeFlowchartProcess, 0, x, 48, 12.75).Select
    Selection.ShapeRange.Fill.ForeColor.SchemeColor = 11
    Selection.ShapeRange.Fill.Solid
    Selection.ShapeRange.Fill.Visible = msoTrue

    Next x

End Sub

So you want to sum a range based on a condition eh? For instance you want to sum the total for just one person in a range of names? Like this picture sums the range A2:A10 on the condition the cell equals “Mark”, and does this in cell D3: The easiest way is with Sumif, but here’s another just for fun.

The syntax to do this:

=Sum(If(RangeWithCondition=”condition”,RangeToGetSum,0))

And hit Control, Shift and Enter to make it an array formula, it won’t work without doing this. (this adds the Braces {} around your formula)

In a cell you want a conditional sum:

1. Type =Sum(If(
2. Click and Drag the Range with the condition, in this example I click and dragged A2 to A10, this automatically filled my formula
3. Type equals (=)
4. In quotation marks type the condition you want to test for, in this example I typed “Mark”, because I want to sum if this condition is true
5. Type comma (,)

Now if it finds the specified range has a condition equal to “Mark”, we need to tell it where to get the numbers to sum.

6. Click and Drag the Range with the numbers to sum if the condition is true
7. Type ,0)) (this will display zero if nothing is found and closes the formula)
8. And most importantly, hit Control, Shift and Enter to make it an array formula

When using VBA to code the Colorindex (or background color) of a cell it is useful to know what integer will equal what color. Below is a reference picture which shows the color and lists it’s respective Colorindex. aka VBA Color Palette

Sub ColorRef()
Dim x As Integer

For x = 1 To 56

If x < 29 Then
Cells(x, 1).Interior.ColorIndex = x
Cells(x, 2) = x
Else
Cells(x - 28, 3).Interior.ColorIndex = x
Cells(x - 28, 4) = x
End If

Next x

End Sub

It’s pretty easy to cut and paste from a macro. Here are a few examples. The code works identical for copy, just replace the word cut with copy!

This one cuts and pastes a single cell, a1 over to b1:

Sub OneCell()

    Range("A1").Select
    Selection.Cut
    Range("B1").Select
    ActiveSheet.Paste

    Application.CutCopyMode = False

End Sub

This one cuts and pastes an entire column, A over to B:

Sub OneColumn()

    Range("A:A").Select
    Selection.Cut
    Range("B:B").Select
    ActiveSheet.Paste

    Application.CutCopyMode = False

End Sub

This one cuts and pastes an entire row, 1 over to 2:

Sub OneRow()

    Range("1:1").Select
    Selection.Cut
    Range("2:2").Select
    ActiveSheet.Paste

    Application.CutCopyMode = False

End Sub

Using VBA you may need to write to the first empty cell, or after the last row used in a column. There is no need to loop to find this, the following code does it for you.

In this example the code will write “FirstEmpty” in the first empty cell in column “d”

Public Sub AfterLast()

ActiveSheet.Range("d" & ActiveSheet.Rows.Count) _
    .End(xlUp).Offset(1, 0).Value = "FirstEmpty"

End Sub

How do you count the blank spaces in a range? With the COUNTBLANK function of course.

1. In a cell you want the result type =COUNTBLANK(
2. Select the range you want to count the blanks in (this automatically fills your formula)
3. Hit enter

In the picture cells B3 and D2 are blank, the result of the COUNTBLANK function is in cell D6

Automate Excel has changed a few times throughout the years. Here is our story:

Version 1
5-15-2004 to 8-16-2004

Version 2
8-16-2004 to 8-1-2008

Version 3 (current)
8-1-2008 to ???

The COMBIN() function is worth a look if you ever have a set number pool to draw a limited number of items from and need to know the maximum combinations. Confused? The lottery is a perfect example, particuliarly any lottery that draws all of the balls from the same pool. In this example let’s use a fake lottery where we have 6 balls drawn, each one can be between 0 and 42. What is the maximum number of combinations that can be drawn?

Tip: COMBIN() Find Maximum Combinations

We can find out the answer to the lottery example by using the COMBIN() function. Here is a look at the syntax:

=COMBIN(NumberInPool,DrawCount)

So we have the number in pool as 42 and our draw count is 6. This returns the maximum number of combinations as 5245786 (aka your odds). The picture shows a couple smaller examples and the lottery example in action:

SideNote: This doesn’t accommodate unique strings, ie, 1-2-3-4-5-6 is the same as 6-5-4-3-2-1 and equals 1 combination.

SideNote: For the true lottery buffs, this doesn’t work for balls drawn from multiple pools.

SideNote: Obviously you can apply this to more than just lottery numbers.

You can use the INFO() function to return the excel version number in a cell. For instance, I use Excel 2003 so the following example returns 11.0

Simply enter the following formula in a cell to return the excel version:

=INFO(”release”)

If you have a time in the format of 1:30:00 but want it to display as the integer 90, here’s the formula.

1. Multiply the time you want to convert to a number by 1440
2. Format the cell as number

If you need to substitute some characters in a cell with other characters, use SUBSTITUTE.

The layman’s syntax for the SUBSTITUTE function is:

=SUBSTITUTE(WhatCellToSUBSTITUTE, “SUBSTITUTEThisText”, “WithThis”)

How do we use this?

1. In a blank cell type =SUBSTITUTE(
2. Click on the cell you would like to substitute characters in(this automatically fills your formula)
3. Type, (comma)
4. Enter the text in quotation marks you would like substituted
5. Type, (comma)
6. Enter the text in quotation marks you would like to replace the text with and hit enter

HLOOKUP is kin to VLOOKUP. VLOOKUP looks up a value from a Vertical list and HLOOKUP looks up a value from a Horizontal list. Go figure. Here is a quick tutorial on the HLOOKUP function.

In this picture we have a list of People and their respective Sales and a Table showing the Bonus they get if they hit a certain target.

In Column C we want to show the bonus based on sales. We do this by grabbing the Sales, comparing it to the Bonus Target table, and returning the bonus amount using HLOOKUP.
Here is the HLOOKUP in layman’s syntax

=HLOOKUP(CellToLookup ,RangeToLookIn, WhichRowToReturn, ExactMatch?)

And now in practice:

1. In a blank cell that we would like to return a result from the list, type =HLOOKUP(
2. Click on the cell where there is a value to lookup (this will enter the cell in your formula)
3. Hit , (comma)
4. Click and drag to select the entire list to look in
5. Hit, (comma)
6. Type the numerical value of the row you would like to return (Don’t type the actual row number, this refers to the row to return in the data. In this example we return row 2 from our Bonus Target table.
7. Hit ,(comma)
8. Type “True” and hit enter

Sidenote: We type “True” to return an approximate answer. To return an exact match type “False”

Sidenote: If you notice the $ signs in the formula, this make the range we lookin absolute. We did this so we could autofill the formula down and our range to look in always stays the same.

The CountIf function is perfect for counting the number of times an instance of something occurs in a range.

The syntax for the CountIf function is:

=Countif(range, criteria)

How do we use this?

1. In a blank cell type =CountIf(
2. Click and drag the range to look in (this automatically fills your formula)
3. Type, (comma)
4. Enter the criteria and hit enter.

For example:
To count numbers in a range greater than 100 your criteria is “>100″ or
=CountIf(A1:A10,”>100″)

To count the total times a word appears in a range your criteria is “yourwordhere” or
=CountIf(A1:A10,”yourwordhere”)

The SumIf function works perfect when you need to Sum a list by a certain criteria.

In this example we want to sum the list in cell A9 only for a name we type in cell A8

We can do this using the SumIf function. The basic SumIf syntax looks like this:

=SumIf(Range, Criteria, Sum Range)

So how do we do this?

1. In a cell where you want the sum to appear type =SumIf(
2. Click and drag the range to match against (A2:A6)
3. Type , (comma)
4. Type your criteria next. In this example we want to sum when our name in cell A8 is equal to a name in our range, so we type this “=” & A8
5. Type , (comma)
6. Click and drag the range to pull the sums from (B2:B6) and hit Enter

Are you looking to build a mortgage calculator in Excel? The PMT function is probably what you are looking for. This tutorial will walk you through the basics of creating a mortgage calculator. Keep in mind you can replace the values in the formula with cell locations.

Link:
http://office.microsoft.com/assistance/preview…..

IF Then Else statements look a bit intimidating to newbies, but here are the basics to clear things up.

We will use this picture in our example.

This returns “Oh Yeah” if June sales are greater than April’s and “Uh-oh” if not.

The basic Layman’s syntax for an If then else statement is:
=If(Evaluation,”Istrue”,”IsFalse”)

To make it work:
1. Replace “Evaluation” with the equation to evaluate. In this case we want to display a message IF June sales(B2) are greater than(>) April Sales(A2).
2. Replace “Istrue” with the text to display if June sales are greater than April sales
3. Replace “Isfalse” with the text to display if June sales are less than April sales

=If((AND(Evaluation1,Evaluation2)),”Istrue”,”IsFalse”)

If you would like to display a message if one or the other figures are greater than a value try the OR syntax

=If((OR(Evaluation1,Evaluation2)),”Istrue”,”IsFalse”)