ahuimanu

Chapter 7, Ex. 21

Jun 18th, 2013
135
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  
  2. 'this does not actually solve the whole problem as the problem mentions that id values
  3. 'may occur more than once.  However, it is close enough to understand
  4. Sub FindBigOrders()
  5.  
  6.     'declare variables
  7.    Dim ws As Worksheet
  8.     Dim cell As Range
  9.     Dim idCell As Range
  10.     Dim amountCell As Range
  11.     Dim counter As Integer
  12.    
  13.     Dim id As Integer
  14.     Dim amount As Integer
  15.        
  16.     counter = 2
  17.     'this is discussed in previous chapters, but also on pages 136 and 137
  18.    'in this case I am accessing the worksheets in the current workbook named "Results"
  19.    Set ws = ActiveWorkbook.Worksheets("Results")
  20.    
  21.     'review page 137 to see how to use the for each loop with a range
  22.    For Each cell In Range("amounts")
  23.         'does the value meet the criteria?
  24.        If cell.Value > 2000 Then
  25.             'get the values
  26.            id = cell.Offset(0, -1).Value
  27.             amount = cell.Value
  28.            
  29.             'write them to the other sheet
  30.            Set idCell = ws.Cells(counter, 1)
  31.             idCell.Value = id
  32.             Set amountCell = ws.Cells(counter, 2)
  33.             amountCell.Value = amount
  34.            
  35.             'only advance the counter if we find a match
  36.            counter = counter + 1
  37.    
  38.         End If
  39.        
  40.     Next
Advertisement
Add Comment
Please, Sign In to add comment