API tools faq
paste
Guest User

Untitled

a guest
Jan 19th, 2018
88
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 6.72 KB | None | 0 0
raw download clone embed print report
  1. cell.Value = Replace(Left(str2, 7), "-", "") & Replace(Left(str2, 7), ",", ".")
  2.  
  3. For Each cell In Range("o2:o50000")
  4. Dim str2 As String
  5. If cell.Value <> "" Then
  6. str2 = cell.Value
  7. cell.Value = Replace(Left(str2, 7), "-", "") 'Left(str2, 6)
  8. End If
  9. Next cell
  10.  
  11. Private Sub UserForm_Initialize()
  12. If Dir("C:UsersibruDocumentspi2.xls") <> "" Then
  13. Workbooks.Open Filename:="C:UsersibruDocumentspi2.xls"
  14. Else
  15. If Dir("C:UsershsegDocumentspi2.xls") <> "" Then
  16. Workbooks.Open Filename:="C:UsershsegDocumentspi2.xls"
  17. Else
  18. If Dir("C:UsersjlamDocumentsexportedexcellfilespi2.xls") <> "" Then
  19. Workbooks.Open Filename:="C:UsersjlamDocumentsexportedexcellfilespi2.xls"
  20. Else
  21. If Dir("e:pi2.xls") <> "" Then
  22. Workbooks.Open Filename:="e:pi2.xls"
  23. Else
  24. If Dir("f:pi2.xls") <> "" Then
  25. Workbooks.Open Filename:="f:pi2.xls"
  26. Else
  27. If Dir("g:pi2.xls") <> "" Then
  28. Workbooks.Open Filename:="g:pi2.xls"
  29. Else
  30. If Dir("h:pi2.xls") <> "" Then
  31. Workbooks.Open Filename:="h:pi2.xls"
  32. Else
  33. If Dir("i:pi2.xls") <> "" Then
  34. Workbooks.Open Filename:="i:pi2.xls"
  35. Else
  36. If Dir("j:pi2.xls") <> "" Then
  37. Workbooks.Open Filename:="j:pi2.xls"
  38. Else
  39. If Dir("k:pi2.xls") <> "" Then
  40. Workbooks.Open Filename:="k:pi2.xls"
  41. End If
  42. End If
  43. End If
  44. End If
  45. End If
  46. End If
  47. End If
  48. End If
  49. End If
  50. End If
  51. TextBox11.Value = Format(Now, "long date")
  52.  
  53. Range("ad2:ad50000").Copy Destination:=Range("aC2:aC50000")
  54.  
  55. ComboBox6 = Format(ComboBox6, "dd/mm/yyyy")
  56. With ComboBox6
  57. For Each cell In Range("ad2:ad50000")
  58. If cell.Value <> "" Then
  59. cell.Value = Format(cell.Value, "dd/mm/yyyy")
  60. cell.Value = Replace(cell.Value, "dd-mm-yyyy", "dd/mm/yyyy")
  61. ComboBox6.AddItem ""
  62. ComboBox6.AddItem Format(cell.Value, "dd/mm/yyyy")
  63.  
  64.  
  65.  
  66.  
  67. End If
  68. Next cell
  69.  
  70. Dim i As Long
  71. Dim j As Long
  72. For i = 0 To .ListCount + 1
  73. For j = .ListCount - 1 To (i + 1) Step -1
  74. If .List(j) = .List(i) Then
  75. .RemoveItem j
  76. End If
  77. Next
  78. Next
  79.  
  80. End With
  81.  
  82. With ComboBox1
  83. For Each cell In Range("ag2:ag50000")
  84. If cell.Value <> "" Then
  85. ComboBox1.AddItem cell.Value
  86.  
  87.  
  88. End If
  89. Next cell
  90.  
  91.  
  92. Call RemoveDuplicates(1)
  93.  
  94. End With
  95. With Me.ListBox8
  96. For Each cell In Range("ad2:ad50000").SpecialCells(xlCellTypeVisible)
  97. If cell.Value <> "" Then
  98. If Range("c" & cell.Row).Value > "" Then
  99. If Range("ai" & cell.Row).Value > "" Then
  100. val1 = (Format(Range("c" & cell.Row).Value))
  101. val2 = (Format(Range("ai" & cell.Row).Value))
  102. .AddItem cell.Row
  103. .List(.ListCount - 1, 0) = Left(val1, 5)
  104. .List(.ListCount - 1, 1) = Left(val2, 3)
  105.  
  106. End If
  107. End If
  108. End If
  109.  
  110. Next cell
  111. Sort = True
  112. Call RemoveDupli(1)
  113.  
  114. End With
  115.  
  116.  
  117. Range("Ad2:Ad50000").NumberFormat = "dd/mm/yyyy"
  118. Range("Ae2:Ae50000").NumberFormat = "hh:mm:ss"
  119. Range("Ac2:Ac50000").NumberFormat = "hh:mm:ss"
  120.  
  121. ComboBox6 = Format(ComboBox6, "dd/mm/yyyy")
  122.  
  123. For Each cell In Range("c2:c50000")
  124. Dim str As String
  125. If cell.Value <> "" Then
  126. str = cell.Value
  127. cell.Value = Left(str, 6)
  128. End If
  129. Next cell
  130.  
  131.  
  132. For Each cell In Range("o2:o50000")
  133. Dim str2 As String
  134. If cell.Value <> "" Then
  135. str2 = cell.Value
  136. cell.Value = Replace(Replace(Left(str2, 7), "-", ""), ",", ".")
  137. End If
  138. Next cell
  139.  
  140.  
  141.  
  142.  
  143.  
  144. With Sheets("Sheet0")
  145. Dim r As Range, n As Long
  146. n = Cells(Rows.count, 1).End(xlUp).Row
  147. Set r = Range("A1:A" & n).Cells.SpecialCells(xlCellTypeVisible)
  148.  
  149. TextBox134.Value = ""
  150. TextBox134.Value = r.count - 1
  151. End With
  152.  
  153. For Each cell In Range("af2:af50000").SpecialCells(xlCellTypeVisible)
  154. If cell.Value > TimeValue("00:15:00") Then
  155.  
  156.  
  157. '12/11/17
  158. With Me.ListBox10
  159.  
  160. .AddItem cell.Row
  161. .List(.ListCount - 1, 0) = cell.Row
  162.  
  163. If Range("ag" & cell.Row).Value = "inp10 - Mireille" Then .List(.ListCount - 1, 1) = "Mireille"
  164. If Range("ag" & cell.Row).Value = "inp14 - Fouzia" Then .List(.ListCount - 1, 1) = "Fouzia"
  165. If Range("ag" & cell.Row).Value = "inp13 - Charline" Then .List(.ListCount - 1, 1) = "Charline"
  166. If Range("ag" & cell.Row).Value = "inp1 - Jacqi" Then .List(.ListCount - 1, 1) = "Jacqi"
  167. If Range("ag" & cell.Row).Value = "inp4 - Kimberley" Then .List(.ListCount - 1, 1) = "Kimberly"
  168. If Range("ag" & cell.Row).Value = "inp5 - Charlene" Then .List(.ListCount - 1, 1) = "Charlene"
  169. If Range("ag" & cell.Row).Value = "inp8 - Mieke" Then .List(.ListCount - 1, 1) = "Mieke"
  170. If Range("ag" & cell.Row).Value = "JOB4 - job4 student" Then .List(.ListCount - 1, 1) = "Lisa"
  171. If Range("ag" & cell.Row).Value = "pick1 - Sorin" Then .List(.ListCount - 1, 1) = "Denis"
  172. If Range("ag" & cell.Row).Value = "pick1 - Denis" Then .List(.ListCount - 1, 1) = "Denis"
  173. If Range("ag" & cell.Row).Value = "pick2 - Abdel" Then .List(.ListCount - 1, 1) = "Abdel"
  174. If Range("ag" & cell.Row).Value = "pick3 - Pieter-Jan" Then .List(.ListCount - 1, 1) = "Pieter-Jan"
  175. If Range("ag" & cell.Row).Value = "pick4 - Hugo" Then .List(.ListCount - 1, 1) = "Hugo"
  176. If Range("ag" & cell.Row).Value = "pick5 - Nicolas" Then .List(.ListCount - 1, 1) = "Nicolas"
  177. If Range("ag" & cell.Row).Value = "pick6 - MO" Then .List(.ListCount - 1, 1) = "Dieter"
  178. If Range("ag" & cell.Row).Value = "pick6 - Dieter" Then .List(.ListCount - 1, 1) = "Dieter"
  179. If Range("ag" & cell.Row).Value = "pick9 - Sebastien" Then .List(.ListCount - 1, 1) = "Sebastien"
  180. If Range("ag" & cell.Row).Value = "inb1 - Francky" Then .List(.ListCount - 1, 1) = "Francky"
  181. If Range("ag" & cell.Row).Value = "inb3 - Kevin" Then .List(.ListCount - 1, 1) = "Kevin"
  182. If Range("ag" & cell.Row).Value = "inp2 - Daniela" Then .List(.ListCount - 1, 1) = "Daniela"
  183. If Range("ag" & cell.Row).Value = "marsh2 - Valerie" Then .List(.ListCount - 1, 1) = "Valerie"
  184. If Range("ag" & cell.Row).Value = "reach - Ludovic" Then .List(.ListCount - 1, 1) = "Ludovic"
  185. If Range("ag" & cell.Row).Value = "SV2 - Jurgen" Then .List(.ListCount - 1, 1) = "Jurgen"
  186. If Range("ag" & cell.Row).Value = "SV1 - Ilse" Then .List(.ListCount - 1, 1) = "Ilse"
  187. If Range("ag" & cell.Row).Value = "CVDM - Carl Van De Moortele" Then .List(.ListCount - 1, 1) = "Carl VDM"
  188.  
  189. .List(.ListCount - 1, 2) = (Format(Range("af" & cell.Row).Value, "h:mm:ss"))
  190. .List(.ListCount - 1, 3) = " Voor "
  191. .List(.ListCount - 1, 4) = (Format(Range("w" & cell.Row).Value))
  192.  
  193. End With
  194.  
  195.  
  196. End If
  197. Next cell
  198.  
  199. Sort = True
  200.  
  201.  
  202.  
  203.  
  204.  
  205. If ToggleButton1.Enabled = True Then
  206. ToggleButton1.Caption = "Foto Uit"
  207. Else
  208. ToggleButton1.Caption = "Foto Aan"
  209. End If
  210.  
  211. End Sub
  212.  
  213. this is my vba code initialize when opening the pi2.xls
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!