How do I copy a range with an Excel macro? (And over Range macros)

To copy data from a specific range can be done with the following macro in Microsoft Excel 2007. In this example, data is copied from the current sheet to the active cell.

Sub CopyRange()
Range("A1:A3").Copy Destination:=ActiveCell
End Sub

To copy from a range in another sheet, Sheet4 in this case, to the active cell you need to change the code to:

Sheets("sheet4").Range("A1:A3").Copy Destination:=ActiveCell

To specify a macro to go to a specific range you can use the Goto method. Here I have already named a range in my worksheet called "Engineering". You may also use an alternative method like the Range select method. Naming a range in Excel is recommended rather than specifying an absolute cell reference.

Sub GoHere()
Application.Goto Reference:="Engineering" OR Range("Engineering").Select
End Sub

Assigning range names to a range of cells.

Sub RngName()
Selection.Name = "myRange"
End Sub

How do I compute the total number of configurations of two or more lists in Microsoft Excel 2007?

How do I compute the total number of configurations of two or more lists in Microsoft Excel 2007? The formulas you will need use are the INT, MOD, COUNTA, ROW, and OFFSET functions. Here is an explanation of each:

INT rounds a number down to the nearest integer.
MOD Returns the remainder from division: MOD(number,divisor).
COUNTA function counts the number of cells that are not empty in a range.
ROW(reference) is the cell or range of cells for which you want the row number.
Row ( ) If reference is omitted, it is assumed to be the reference of the cell in which the ROW function appears.
Example: =ROW( ) - 1 inserted into a cell in row 26 will yield the result 25.


Here are the formulas for a two column combination:
=OFFSET($A$1,INT((ROW()-1)/(COUNTA(B:B))),0)
=OFFSET($A$1,INT(MOD((ROW()-1),COUNTA(B:B))),1)

First Column Formula Explanation:

=OFFSET($A$1,INT((ROW()-1)/(COUNTA(B:B))),0)

Takes the row number and subtracts one from it. Then it divides this number by the number of options in the second column. This value is then rounded down to the nearest integer (whole number). This in turn is the number of the row in which it will select the answer, starting with 0.




Later on I will give an example of to use this formula up to ten unique columns.

Excel Spreadsheet Template Example: Track Record

One of the activities that I love to do is to travel to amusement parks across the United States and ride roller coasters. I also love looking at numbers and data. I decided to combine the two using Excel. I have created a track record spreadsheet, a list of all of the roller coaster I have ridden along with several statistics I was interested in, such as height, speed, length, and inversions. This is my explanation of how I made it. Your spreadsheet doesn’t necessarily have to be for roller coasters, it could be for any other hobbies or interests that you have that you want to look at stats for.

The first thing I did was list all of the amusement parks I have visited that I could remember. This is placed on a sheet in the workbook that I called the “Master List” because, well, it contains all of the master lists used later on. In the next column I listed all fifty states. The next category is type of coaster- wood or steel. I also decided to make up a class type and finally, I listed all the roller coaster manufacturers I came across while looking up data on rcdb.com.

The next sheet of the workbook is where all of the individual coasters that I have ridden are listed. The first column is the name of the coaster, inputted by me. Under column B is listed the park where the coaster resides. Now for these entries you simply have to pick the name of the park on a drop down list which is generated from the first sheet. The same goes for the state, type, class, and manufacturer. I use drop down lists so you don’t have to do a lot of typing or copy and pasting. It reduces possible error which makes the final data more accurate.

How do you create the drop down lists in Excel? You simply use the name manager to name your lists and then use data validation to actually change the cell from default to a list. Here are two quick pictures. If you need further explanation go here.

The next section is all of the data I wanted to look at. This is all from rcdb.com. Categories I were interested in and available were length, height, drop, speed, inversions, year opened and year closed. As you can see there are a few holes but that is ok because those  are mostly on the smaller, kiddie rides. The last section is where the user needs to input their own data. I decided to list the number of times I have been on the ride, when my first ride was, and the last year it was that I rode. This section isn’t necessarily but I thought it would be fun to look at.

Now it’s time to take a look at the data, which I placed on the third sheet.  In the first column I have called up the list of visited amusement parks from the Master List tab by using the formula =’Master Lists’!B2. The next column I have is to compute the number of roller coasters I have been on at each amusement park. I used this formula:
=IF(COUNTIF(Data!B:B, Stats!A2)=0, “”, COUNTIF(Data!B:B, Stats!A2))
which basically looks up the park listed in the first column, for example “Holiday World” and it goes through the column B in the Data sheet (the park column) and counts how many roller coasters are associated with that park, Holiday World. In this case, I have been on four roller coasters at Holiday World (Voyage, Raven, Legend, and Howler).

As you can see, Cedar Point is the park I have visited with the most coasters, 18 (I counted Gemini as two separate rides. Some would argue this point but when you make up your own track record you make up your own rules!) You can also see I visited Legoland California but did not ride any of the coasters. They looked far too scary!

I used the same formulas for the next section, the states. I’ve been on coasters in ten different states. Ohio had by far the most coasters with 44, which should also come as no surprise since I lived there for 23 years and it had three great parks. Florida comes in second with 18 and PA close behind with 16. The total number of coasters is 116. This is a good place to double check your work. If your total number comes out the same here as in the first section everything is fine, but if your totals don’t match up then there is a problem somewhere. Go back and double check all of your formulas. Next is the type of coaster. 70% of the coaster I have ridden have been steel versus 30% wood structure.
Next is our classes and manufacturer’s data. I’ve been on 20 roller coaster built by Arrow Dynamics, which is no surprise because they were THE coaster company for a good number of years. Next is Vekoma and B&M.

Now we get to the actual statistics. By listing all of the lengths for each coaster I can come up with an average, max, and min. These formulas are quite simple: =AVERAGE(Data!G3:G201), =MAX(Data!G3:G201), =MIN(Data!G3:G201).
The average length of a coaster is 2964 feet, height is 107, drop is 122.7’, speed is 53.2, and goes upside down one time.  I’ve traveled over 54 miles on different roller coasters! The Beast was the longest coaster and Top Thrill Dragster was the tallest and fastest. Any guesses as to what was the shortest?

Another interesting stat to look at is average age versus average life of a coaster. The average life of a coaster is how long it actually exists. To get this number I took the average year a coaster was closed and subtracted the average year opened to get 17. This is only for rides that have actually closed and been torn down for good. The average age is this year minus  the average year opened. The average age of coasters I have ridden is 20. When one of these rides closes it will bring up the average life statistic. The oldest coaster I rode was the Big Dipper at the now defunct Geauga Lake.

One final  section to look at is the number of new rides I’ve visited each year.  The formula I used is =COUNTIF(Data!P:P, V23) which counts the number of rides  if the years match up. As you can see, 2006 was a great year with 24 new rides. The past two years have been a little disappointing in that regard but here’s hoping for a great 2010!

What other data would you like to see looked at or examined? Please share your thoughts or suggestions!

For more roller coasters be sure to check out Coaster101!

How do I organize my Excel spreadsheet macros?

If you have an Excel workbook that is going to have multiple macros, sometimes it is a good idea to create another spreadsheet to organize your macros, especially if many people are going to be using them. In the first column I list the name of the macro. The next column is a brief description. For this particular example, there are several different spreadsheets being used so it is important to know which workbooks need to be open while the macro function is performed, which is listed in the next column. After that I listed the file to actually run the macro from and the name of the output file. The final column is what worksheet is automatically saved by the macro.



As you can see, some of the macros perform the same function but the code is and works differently.




Finally, at the bottom of the sheet I have listed a few notes to help clarify a few possibly confusing issues. The main purpose of this spreadsheet is for clarity and making sure all of the users are on the same page.



Press Alt+F11 to bring up the macro editor. You can import or export macro modules as needed. This is what the Microsoft Visual Basic editor looks like.



Here is my list of macros.



Pictured is one of the actual macros. This one protects the specific cells in the spreadsheet that I don’t want users to ever change. Later on I will get go in further detail on just exactly how these macros work.

How do you make a drop down list in Excel 2007?

To limit entries to certain items that you define you can create a drop-down list of valid entries that is compiled from cells elsewhere on the worksheet. When you create a drop-down list for a cell it displays an arrow next to that cell. To enter information in that cell click the arrow and then click the entry that you want.

To create or edit a drop down list, select the cell the list is to be in then go to Data > Data Validation.

Under Allow: select List.

Under Source: select the range of values that you want by either highlighting a group of cells OR by typing the name of a named range (which will be explained in a later post).



DCY2PY25XSC9