1. Course content
  2. Part IV. Data analysis
  3. Summarizing data
  4. Extracting data
  5. Extracting to a worksheet

Extracting to a worksheet

Keywords: 
Loops

The goal

Let's write code that extracts sales above 250. The worksheet starts out as:

Start

When the button is clicked, the program extracts the values above 250 to another part of the worksheet. It also shows the size of the new data set:

End

How does the program work? Basically, it runs through the original data. When it finds a value that's OK, it copies that value to the new data set.

Finding the end

The amount of sales data could change from day to day. Let's start with the usual code for finding the end of a data set:

  1. Private Sub cmdRun_Click()
  2.     Dim rowSource As Integer
  3.     rowSource = 0
  4.     Do While Cells(rowSource + 1, 1) <> ""
  5.         rowSource = rowSource + 1
  6.     Loop
  7. End Sub
This uses look-ahead. It keeps looping until it finds an empty cell. If you need a reminder, check out Finding the end.

The index variable has a different name from before. The reason will become clear in a moment.

Recall that an index variable refers to a location where data is stored. In Cells(row, 1), row is an index variable. It says which row to use.

Testing for high sales

Now, let's add code to test whether each value should be copied to the new location.

  1. Private Sub cmdRun_Click()
  2.     Dim rowSource As Integer
  3.     rowSource = 0
  4.     Do While Cells(rowSource + 1, 1) <> ""
  5.         rowSource = rowSource + 1
  6.         'Is the data above 250?
  7.         If Cells(rowSource, 1) > 250 Then
  8.             'Copy value to destination.
  9.             ???
  10.         End If
  11.     Loop
  12. End Sub
Tara
Tara
What now? Remember, here's what we want.

Goal

The data is the third column is an extract of the data in the first column.

Jeremy
Jeremy
OK, so something like:

Cells(??, 3) = Cells(??, 1)

Take something from column 1 (right of the =), and put it in column 3 (left of the =).

Tara
Tara
Great! Let's add that in.

  1. Private Sub cmdRun_Click()
  2.     Dim rowSource As Integer
  3.     rowSource = 0
  4.     Do While Cells(rowSource + 1, 1) <> ""
  5.         rowSource = rowSource + 1
  6.         'Is the data above 250?
  7.         If Cells(rowSource, 1) > 250 Then
  8.             'Copy value to destination.
  9.             ???
  10.             Cells(??, 3) = Cells(??, 1)
  11.             ???
  12.         End If
  13.     Loop
  14. End Sub
Simon
Simon
Oh, hey! We know what cell the data is coming from. We referred to it in line 6:

If Cells(rowSource, 1) > 250 Then

It's Cells(rowSource, 1). That's where the data is coming from.

Tara
Tara
Cool!

  1. Private Sub cmdRun_Click()
  2.     Dim rowSource As Integer
  3.     rowSource = 0
  4.     Do While Cells(rowSource + 1, 1) <> ""
  5.         rowSource = rowSource + 1
  6.         'Is the data above 250?
  7.         If Cells(rowSource, 1) > 250 Then
  8.             'Copy value to destination.
  9.             ???
  10.             Cells(??, 3) = Cells(rowSource, 1)
  11.             ???
  12.         End If
  13.     Loop
  14. End Sub
Tara
Tara
So, how do we figure out what cell the data is going into?

Cells(??, 3) = Cells(rowSource, 1)

What cell will the first piece of copied data go into?

Klaus
Klaus
Cell(1, 3), right?

First value goes here

Klaus
Klaus
The second one goes right below.

Second value goes here

Lily
Lily
I get it! We'll need another index variable, to keep track of where to put the new data. Row 1, then row 2, then row 3, like that.

The new index variable will be for the destination of the copied value.

Tara
Tara
Right!

  1. Private Sub cmdRun_Click()
  2.     Dim rowSource As Integer
  3.     Dim rowDestination as Integer
  4.     rowSource = 0
  5.     rowDestination = 0
  6.     Do While Cells(rowSource + 1, 1) <> ""
  7.         rowSource = rowSource + 1
  8.         'Is the data above 250?
  9.         If Cells(rowSource, 1) > 250 Then
  10.             'Copy value to destination.
  11.             rowDestination = rowDestination + 1
  12.             Cells(rowDestination, 3) = Cells(rowSource, 1)
  13.         End If
  14.     Loop
  15. End Sub
There are now two index variables in the code. The first one, rowSource, runs over the original data set, the data source. rowSource goes up by one every time through the Do loop (line 13).

The second index variable, rowDestination, tracks where new data is copied to. The destination. It goes up by 1, as well (line 10). But it only goes up when sales are more than 250.

Showing the count

One more thing we need to do: show the number of data elements copied:

One more thing

Lily
Lily
We already have a variable for that!

  1. Private Sub cmdRun_Click()
  2.     Dim rowSource As Integer
  3.     Dim rowDestination as Integer
  4.     rowSource = 0
  5.     rowDestination = 0
  6.     Do While Cells(rowSource + 1, 1) <> ""
  7.         rowSource = rowSource + 1
  8.         'Is the data above 250?
  9.         If Cells(rowSource, 1) > 250 Then
  10.             'Copy value to destination.
  11.             rowDestination = rowDestination + 1
  12.             Cells(rowDestination, 3) = Cells(rowSource, 1)
  13.         End If
  14.     Loop
  15.     Cells(5, 2) = rowDestination
  16. End Sub
That's the program! So, to extract data to the worksheet:

  • Pick where the extracted data should go. That's the destination.
  • Create an index variable for the destination.
  • Each time there's a value to copy, copy it to the destination, and increment (add one to) the new index variable.

Exercises

Exercise: Extract the quackers
Duck "Earlier", you marked the quackiest ducks. This time, copy their data to a different part of the worksheet. Still mark bad data.

Start:

Start

Sample output:

Output

Start with this workbook, and add your own code.

Upload your workbook. The usual coding standards apply, as always.

In the text box on the submission form, please answer this question: What did you learn from this exercise?

Attachments

duck-quacks-extract-start.xlsm

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)

Exercise: Extract large price changes
Earlier, you did an exercise where you marked data showing large price changes during each day. Do it again, but this time extract the data.

The worksheet starts like this:

Start

When the user clicks Run, your program extracts data. Data from days where the price increased by 2 or more is copied into the "Large increase" section. Data from days where the price fell by 2 or more is copied into the "Large decrease" section. Here's some sample output:

Output

Download this worksheet and add the code to it.

Hints:

  • The first rows don't contain data. You'll need to adjust for that.
  • You'll need three counters. One for the input rows, and one each for the output regions.

Upload your solution. The usual coding standards apply.

When you submit, please answer this question: What did you learn from this exercise? Type in the text area of the solution form.

Attachments

extract-price-changes-start.xlsm

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)

Summary

Start with the usual code for finding the end of a data set. Add code to test whether each value should be copied to the new location.

There are two index variables in the code. The first one runs over the original data set, the data source. The second one tracks where new data is copied to, the destination.