API tools faq
paste
Trai60

ProlificCSVMacro5.bas

Trai60
May 17th, 2024
95
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.92 KB | Software | 0 0
raw download clone embed print report
  1. Attribute VB_Name = "Prolific05"
  2. Sub Prolific03()
  3. Attribute Prolific03.VB_ProcData.VB_Invoke_Func = " \n14"
  4. '
  5. ' Prolific03 Macro
  6. '
  7.  
  8. '
  9. With ActiveWindow
  10. .SplitColumn = 0
  11. .SplitRow = 1
  12. End With
  13. ActiveWindow.FreezePanes = True
  14. ActiveWindow.SmallScroll ToRight:=1
  15. Columns("F:F").Select
  16. Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  17. Range("my_prolific_submission_history[[#Headers],[Column1]]").Select
  18. ActiveCell.FormulaR1C1 = "Time Taken"
  19. Range("F2").Select
  20. Range("my_prolific_submission_history[Time Taken]").FormulaR1C1 = _
  21. "=IF(RC[-1]<>"""", RC[-1]-RC[-2], """")"
  22. Columns("F:F").Select
  23. Selection.NumberFormat = "[h]:mm:ss"
  24. Columns("B:B").Select
  25. Selection.Cut
  26. ActiveWindow.SmallScroll ToRight:=2
  27. Range("I1").Select
  28. Selection.Insert Shift:=xlToRight
  29. Columns("H:H").Select
  30. Selection.TextToColumns Destination:=Range( _
  31. "my_prolific_submission_history[[#Headers],[Reward]]"), DataType:=xlDelimited _
  32. , TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
  33. Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
  34. :="$", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
  35. ActiveWindow.ScrollColumn = 3
  36. ActiveWindow.ScrollColumn = 2
  37. ActiveWindow.ScrollColumn = 1
  38. ActiveWindow.ScrollColumn = 2
  39. ActiveWindow.ScrollColumn = 3
  40. ActiveWindow.ScrollColumn = 4
  41. Range("J2").Select
  42. ActiveWindow.ScrollColumn = 3
  43. ActiveWindow.ScrollColumn = 2
  44. ActiveWindow.ScrollColumn = 1
  45. Columns("B:B").Select
  46. Selection.Cut
  47. ActiveWindow.ScrollColumn = 2
  48. ActiveWindow.ScrollColumn = 3
  49. ActiveWindow.SmallScroll ToRight:=2
  50. Range("J1").Select
  51. Selection.Insert Shift:=xlToRight
  52. Columns("I:I").Select
  53. Selection.TextToColumns Destination:=Range( _
  54. "my_prolific_submission_history[[#Headers],[Bonus]]"), DataType:=xlDelimited _
  55. , TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
  56. Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
  57. :="$", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
  58. Range("my_prolific_submission_history[[#Headers],[Column1]]").Select
  59. ActiveCell.FormulaR1C1 = "Reward $"
  60. Range("my_prolific_submission_history[[#Headers],[Column2]]").Select
  61. ActiveCell.FormulaR1C1 = "Bonus $"
  62. Range("my_prolific_submission_history[[#Headers],[Reward]]").Select
  63. ActiveCell.FormulaR1C1 = "Reward £"
  64. Range("my_prolific_submission_history[[#Headers],[Bonus]]").Select
  65. ActiveCell.FormulaR1C1 = "Bonus £"
  66. Range("K2").Select
  67. Range("G2").Select
  68. ActiveCell.FormulaR1C1 = "£2.50"
  69. Cells.Replace What:="£", Replacement:="", LookAt:=xlPart, SearchOrder _
  70. :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False _
  71. , FormulaVersion:=xlReplaceFormula2
  72. Range("G:G,I:I").Select
  73. Range("my_prolific_submission_history[[#Headers],[Bonus £]]").Activate
  74. Selection.NumberFormat = _
  75. "_-[$£-en-GB]* #,##0.00_-;-[$£-en-GB]* #,##0.00_-;_-[$£-en-GB]* ""-""??_-;_-@_-"
  76. Range("H:H,J:J").Select
  77. Range("my_prolific_submission_history[[#Headers],[Bonus $]]").Activate
  78. Selection.NumberFormat = _
  79. "_-[$$-en-US]* #,##0.00_ ;_-[$$-en-US]* -#,##0.00 ;_-[$$-en-US]* ""-""??_ ;_-@_ "
  80. Columns("G:J").Select
  81. Selection.SpecialCells(xlCellTypeBlanks).Select
  82. Selection.FormulaR1C1 = "0"
  83. Columns("G:J").Select
  84. Selection.Columns.AutoFit
  85. ActiveWindow.ScrollColumn = 4
  86. ActiveWindow.ScrollColumn = 3
  87. Columns("G:G").Select
  88. Selection.Cut
  89. ActiveWindow.ScrollColumn = 2
  90. ActiveWindow.ScrollColumn = 1
  91. Range("my_prolific_submission_history[[#Headers],[Started At]]").Select
  92. Selection.Insert Shift:=xlToRight
  93. ActiveWindow.ScrollColumn = 2
  94. Columns("I:I").Select
  95. Selection.Cut
  96. Range("my_prolific_submission_history[[#Headers],[Started At]]").Select
  97. Selection.Insert Shift:=xlToRight
  98. Columns("I:I").Select
  99. Selection.Cut
  100. Range("my_prolific_submission_history[[#Headers],[Started At]]").Select
  101. Selection.Insert Shift:=xlToRight
  102. Columns("J:J").Select
  103. Selection.Cut
  104. Range("my_prolific_submission_history[[#Headers],[Started At]]").Select
  105. Selection.Insert Shift:=xlToRight
  106. ActiveWindow.ScrollColumn = 1
  107. Cells.Select
  108. Selection.Columns.AutoFit
  109. Range("A2").Select
  110. Sheets("Sheet1").Select
  111. Range("A1").Select
  112. ActiveCell.FormulaR1C1 = "Results"
  113. Range("A3").Select
  114. ActiveCell.FormulaR1C1 = "Approved"
  115. Range("A4").Select
  116. ActiveCell.FormulaR1C1 = "Returned"
  117. Range("A5").Select
  118. ActiveCell.FormulaR1C1 = "Awaiting Review"
  119. Range("A6").Select
  120. ActiveCell.FormulaR1C1 = "Rejected"
  121. Range("A8").Select
  122. ActiveCell.FormulaR1C1 = "Approval Rating"
  123. Range("A10").Select
  124. ActiveCell.FormulaR1C1 = "Time Worked"
  125. Range("A12").Select
  126. ActiveCell.FormulaR1C1 = "Reward £"
  127. Range("A13").Select
  128. ActiveCell.FormulaR1C1 = "Bonus £"
  129. Range("A14").Select
  130. ActiveCell.FormulaR1C1 = "Reward $"
  131. Range("A15").Select
  132. ActiveCell.FormulaR1C1 = "Bonus $"
  133. Range("A17").Select
  134. ActiveCell.FormulaR1C1 = "Total $"
  135. Range("A18").Select
  136. ActiveCell.FormulaR1C1 = "Total £ Converted to $"
  137. Range("A20").Select
  138. ActiveCell.FormulaR1C1 = "Total Earned"
  139. Range("A22").Select
  140. ActiveCell.FormulaR1C1 = "P/H Rate"
  141. Range("B3").Select
  142. ActiveCell.FormulaR1C1 = _
  143. "=COUNTIF(my_prolific_submission_history!C[8], ""APPROVED"")"
  144. Range("B4").Select
  145. ActiveCell.FormulaR1C1 = _
  146. "=COUNTIF(my_prolific_submission_history!C[8], ""RETURNED"")"
  147. Range("B5").Select
  148. ActiveCell.FormulaR1C1 = _
  149. "=COUNTIF(my_prolific_submission_history!C[8], ""AWAITING REVIEW"")"
  150. Range("B6").Select
  151. ActiveCell.FormulaR1C1 = _
  152. "=COUNTIF(my_prolific_submission_history!C[8], ""REJECTED"")"
  153. Range("B8").Select
  154. ActiveCell.FormulaR1C1 = "=TEXT(R[-5]C / (R[-5]C + R[-2]C), ""0.00%"")"
  155. Range("B8").Select
  156. With Selection
  157. .HorizontalAlignment = xlRight
  158. .VerticalAlignment = xlBottom
  159. .WrapText = False
  160. .Orientation = 0
  161. .AddIndent = False
  162. .IndentLevel = 0
  163. .ShrinkToFit = False
  164. .ReadingOrder = xlContext
  165. .MergeCells = False
  166. End With
  167. Range("B10").Select
  168. ActiveCell.FormulaR1C1 = "=SUM(my_prolific_submission_history[Time Taken])"
  169. Range("B12").Select
  170. ActiveCell.FormulaR1C1 = _
  171. "=ROUND(SUMIFS(my_prolific_submission_history!C, my_prolific_submission_history!C[8], ""<>RETURNED"", my_prolific_submission_history!C[8], ""<>REJECTED"", my_prolific_submission_history!C[8], ""<>AWAITING REVIEW""), 2)"
  172. Range("B13").Select
  173. ActiveCell.FormulaR1C1 = "=SUM(my_prolific_submission_history[Bonus £])"
  174. Range("B14").Select
  175. ActiveCell.FormulaR1C1 = _
  176. "=ROUND(SUMIFS(my_prolific_submission_history!C[2], my_prolific_submission_history!C[8], ""<>RETURNED"", my_prolific_submission_history!C[8], ""<>REJECTED"", my_prolific_submission_history!C[8], ""<>AWAITING REVIEW""), 2)"
  177. Range("B15").Select
  178. ActiveCell.FormulaR1C1 = "=SUM(my_prolific_submission_history[Bonus $])"
  179. Range("B17").Select
  180. ActiveCell.FormulaR1C1 = "=SUM(R[-3]C,R[-2]C)"
  181. Range("B18").Select
  182. ActiveCell.FormulaR1C1 = "=SUM(R[-6]C+R[-5]C)*1.2677446"
  183. Range("B20").Select
  184. ActiveCell.FormulaR1C1 = "=SUM(R[-3]C,R[-2]C)"
  185. Range("B22").Select
  186. ActiveCell.FormulaR1C1 = "=R[-2]C / (R[-12]C * 24)"
  187. Range("B10").Select
  188. Selection.NumberFormat = "[h]:mm:ss"
  189. Range("B13").Select
  190. Selection.NumberFormat = _
  191. "_-[$£-en-GB]* #,##0.00_-;-[$£-en-GB]* #,##0.00_-;_-[$£-en-GB]* ""-""??_-;_-@_-"
  192. Range("B15").Select
  193. Selection.NumberFormat = _
  194. "_-[$$-en-US]* #,##0.00_ ;_-[$$-en-US]* -#,##0.00 ;_-[$$-en-US]* ""-""??_ ;_-@_ "
  195. Range("B17").Select
  196. Selection.NumberFormat = _
  197. "_-[$$-en-US]* #,##0.00_ ;_-[$$-en-US]* -#,##0.00 ;_-[$$-en-US]* ""-""??_ ;_-@_ "
  198. Range("B18").Select
  199. Selection.NumberFormat = _
  200. "_-[$$-en-US]* #,##0.00_ ;_-[$$-en-US]* -#,##0.00 ;_-[$$-en-US]* ""-""??_ ;_-@_ "
  201. Range("B14").Select
  202. Selection.NumberFormat = _
  203. "_-[$$-en-US]* #,##0.00_ ;_-[$$-en-US]* -#,##0.00 ;_-[$$-en-US]* ""-""??_ ;_-@_ "
  204. Range("B12").Select
  205. Selection.NumberFormat = _
  206. "_-[$£-en-GB]* #,##0.00_-;-[$£-en-GB]* #,##0.00_-;_-[$£-en-GB]* ""-""??_-;_-@_-"
  207. Range("B20").Select
  208. Selection.NumberFormat = _
  209. "_-[$$-en-US]* #,##0.00_ ;_-[$$-en-US]* -#,##0.00 ;_-[$$-en-US]* ""-""??_ ;_-@_ "
  210. Range("B22").Select
  211. Selection.NumberFormat = _
  212. "_-[$$-en-US]* #,##0.00_ ;_-[$$-en-US]* -#,##0.00 ;_-[$$-en-US]* ""-""??_ ;_-@_ "
  213. Cells.Select
  214. Selection.Columns.AutoFit
  215. Range("B23").Select
  216. End Sub
  217.  
Advertisement
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!