Keywords:

# The goal

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

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:

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
What now? Remember, here’s what we want.

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

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

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
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
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
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
Cell(1, 3), right?

Klaus
The second one goes right below.

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
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:

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
“Earlier”, you marked the quackiest ducks. This time, copy their data to a different part of the worksheet. Still mark bad data.

Start:

Sample output:

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:

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:

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.

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.