ahuimanu

Exercise 18 Code

Dec 16th, 2015
158
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Sub Exercise18MacroOne()
  2. '
  3. ' Exercise18MacroOne Macro
  4. '
  5.  
  6.     'set  the random number generator seed
  7.    Randomize
  8.     Dim MyValue As Single
  9.     MyValue = Rnd
  10.    
  11.     'to get a range we'll have to do some weird things
  12.    'Int ((upperbound - lowerbound + 1) * Rnd + lowerbound)
  13.    With Worksheets("Exercise 18")
  14.         Range("E1").Value = MyValue
  15.     End With
  16.    
  17.  
  18.     'now, let's create an array and write it to some cells
  19.    'in the currently active worksheet
  20.    Dim Fruits(1 To 8) As String
  21.     Fruits(1) = "Bananas"
  22.     Fruits(2) = "Grapes"
  23.     Fruits(3) = "Oranges"
  24.     Fruits(4) = "Apples"
  25.     Fruits(5) = "Peaches"
  26.     Fruits(6) = "Watermelon"
  27.     Fruits(7) = "Papaya"
  28.     Fruits(8) = "Durian"
  29.    
  30.     'For i = 1 To 8
  31.    '    Cells(i, 3) = Fruits(i)
  32.    'Next i
  33.    
  34.     'Origin array
  35.    Dim Origin(1 To 2) As String
  36.     Origin(1) = "Imported"
  37.     Origin(2) = "Local"
  38.    
  39.     'Days array
  40.    Dim Days(1 To 7) As String
  41.     Days(1) = "Monday"
  42.     Days(2) = "Tuesday"
  43.     Days(3) = "Wednesday"
  44.     Days(4) = "Thursday"
  45.     Days(5) = "Friday"
  46.     Days(6) = "Saturday"
  47.     Days(7) = "Sunday"
  48.    
  49.     Dim FruitIndex As Integer
  50.     Dim OriginIndex As Integer
  51.     Dim DaysIndex As Integer
  52.    
  53.     For i = 1 To 1000
  54.         Randomize
  55.         FruitIndex = Int((8 - 1 + 1) * Rnd + 1)
  56.         OriginIndex = Int((2 - 1 + 1) * Rnd + 1)
  57.         'if the fruit is Papaya or Durian, then do not allow index 2 (local)
  58.        If FruitIndex = 7 Or FruitIndex = 8 Then
  59.            OriginIndex = 1
  60.         End If
  61.         DaysIndex = Int((7 - 1 + 1) * Rnd + 1)
  62.    
  63.         'now write to the worksheet
  64.        With Worksheets("Exercise 18")
  65.             'Fruits
  66.            .Cells(i, 1) = Fruits(FruitIndex)
  67.             'Origin
  68.            .Cells(i, 2) = Origin(OriginIndex)
  69.             'Days
  70.            .Cells(i, 3) = Days(DaysIndex)
  71.         End With
  72.     Next i
  73.  
  74. End Sub
  75.  
  76. Sub Exercise18HideDurian()
  77.     'used to hide rows based on whether we find Durian in the data
  78.    Dim BeginRow As Integer
  79.     Dim EndRow As Integer
  80.     Dim CheckColumn As Integer
  81.     BeginRow = 1
  82.     EndRow = 1000
  83.     CheckColumn = 1
  84.  
  85.     'go through and look for Durian
  86.    For RowCount = BeginRow To EndRow
  87.         With Worksheets("Exercise 18")
  88.             If Cells(RowCount, CheckColumn).Value = "Durian" Then
  89.                 Cells(RowCount, CheckColumn).EntireRow.Hidden = True
  90.             End If
  91.         End With
  92.     Next RowCount
  93. End Sub
  94.  
  95. Sub Exercise18ShowDurian()
  96.     'used to show rows based on whether we find Durian in the data
  97.    Dim BeginRow As Integer
  98.     Dim EndRow As Integer
  99.     Dim CheckColumn As Integer
  100.     BeginRow = 1
  101.     EndRow = 1000
  102.     CheckColumn = 1
  103.    
  104.     'go through and look for Durian
  105.    For RowCount = BeginRow To EndRow
  106.         With Worksheets("Exercise 18")
  107.             If Cells(RowCount, CheckColumn).Value = "Durian" Then
  108.                 Cells(RowCount, CheckColumn).EntireRow.Hidden = False
  109.             End If
  110.         End With
  111.     Next RowCount
  112. End Sub
Advertisement
Add Comment
Please, Sign In to add comment