1. koolituspäeval loodud koodilõigud


Sub kihtvalim()
  'Application.ScreenUpdating = False
    plokiotsing2
    valimine
  'Application.ScreenUpdating = True
End Sub

Sub valimsort()
  veerunr = Val(InputBox("Mitmes tulp?"))
  ActiveSheet.Cells.Select
  Selection.Sort Key1:=Cells(2, veerunr), Order1:=xlAscending,
Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
   reanr = 1
   plokialgus = 1
   'Application.ScreenUpdating = False
   While Len(Cells(reanr, veerunr)) > 0
      If Not Cells(reanr, veerunr) = Cells(reanr + 1, veerunr) Then
         Cells(plokialgus, 11) = Range("h" & reanr)
         plokialgus = reanr + 1
         Cells(reanr + 1, 10).Value = "Uus plokk"
         Cells(reanr + 1, 8).Value = "=sum(g$" & (reanr + 1) & ":g" & (reanr + 1) & ")"
      Else
         Cells(reanr + 1, 8).Value = "=h" & reanr & "+ g" & (reanr + 1)
      End If
      reanr = reanr + 1
   Wend
   valimine
   'Application.ScreenUpdating = True

End Sub



Sub plokiotsing()
   reanr = 1
   veerunr = 2
   While Len(Cells(reanr, veerunr)) > 0
      If Not Cells(reanr, veerunr) = Cells(reanr + 1, veerunr) Then
         Cells(reanr + 1, 10) = "Uus plokk"
      End If
      reanr = reanr + 1
   Wend

End Sub

Sub plokiotsing2()
   Dim lahter As Range
   reanr = 1
   veerunr = 2
   plokialgus = 1
   Application.ScreenUpdating = False
   While Len(Cells(reanr, veerunr)) > 0
      If Not Cells(reanr, veerunr) = Cells(reanr + 1, veerunr) Then
         Cells(plokialgus, 11) = Range("h" & reanr)
         plokialgus = reanr + 1
         Set lahter = Cells(reanr + 1, 10)
         lahter = "Uus plokk"
         Cells(reanr + 1, 8).Value = "=sum(g$" & (reanr + 1) & ":g" & (reanr + 1) & ")"
      Else
         Cells(reanr + 1, 8).Value = "=h" & reanr & "+ g" & (reanr + 1)
      End If
      reanr = reanr + 1
   Wend
   Application.ScreenUpdating = True
End Sub

Sub valimine()
   reanr = 2
   veerunr = 2
   plokialgus = 1
   summa = 1
   margitud = False
   While Len(Cells(reanr, veerunr)) > 0
      If Cells(reanr, 10) = "Uus plokk" Then
         margitud = True
         summa = Range("K" & reanr)
      End If
      If margitud Then
         Cells(reanr, 12) = "sees"
      Else
         Cells(reanr, 12) = "väljas"
      End If
      If (Cells(reanr, 8) / summa) > (Range("l1") / 100) Then
         margitud = False
      End If
      reanr = reanr + 1
   Wend
End Sub