Advertisement
Guest User

Untitled

a guest
Mar 29th, 2015
253
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 3.80 KB | None | 0 0
  1. Sub AddSignals()
  2.  
  3. Dim rng1 As Range, rng2 As Range, i As Long, iL As Long, id As Long, idL As Long, var As Range, var2 As Range, j As Long, ws1 As Worksheet, ws2 As Worksheet, jL As Long, rng5 As Range, jFn As Long, iLs As Long, iLss As Range, rng1s As Range, rng2s As Range
  4. Dim rng3 As Range, rng4 As Range, lCols As Long, lRows As Long, SrtRng As Range, Acell As Range, iLs2 As Long, iLss2 As Range, SrtRngF As Range, AcellF As Range
  5. Dim Clnup As Range, Clncol As Long, ClnRow As Long, trimrng As Range, trimrng1 As Range, emptyrange As Range, EmptyCol As Long
  6.  
  7. On Error GoTo ErrHandler
  8.  
  9. Set ws1 = Sheet7
  10. Set ws2 = Sheet2
  11.  
  12. 'Look for new signals and add to signal list
  13. jL = ws1.Cells(7, Columns.Count).End(xlToLeft).Column
  14.  
  15. For j = 3 To jL
  16. 'Set range limits
  17. jF = ws2.Cells(7, Columns.Count).End(xlToLeft).Column
  18. jFn = jF + 1
  19. ws2.Cells(7, jFn).Value = ws1.Cells(7, j).Value
  20.  
  21. idL = ws1.Cells(Rows.Count, j).End(xlUp).Row
  22.  
  23. For id = 8 To idL
  24. iLs2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
  25. Set iLss2 = ws2.Cells(iLs2 + 1, 1)
  26.  
  27. Set rng1s = ws1.Cells(id, j)
  28. If Not IsEmpty(rng1s) Then
  29. Set rng2s = ws2.Range(ws2.Cells(7, 1), ws2.Cells(ws2.Cells(Rows.Count, 1).End(xlUp).Row, 1))
  30. Set var2 = rng2s.Find(rng1s.Value, LookIn:=xlValues, LookAt:=xlWhole)
  31. If var2 Is Nothing Then
  32. bln = True
  33. If bln = True Then
  34. iLss2.Value = rng1s.Value
  35. 'remove any spaces from cells
  36. iLss2.Value = WorksheetFunction.Trim(iLss2.Value)
  37. Set emptyrange = ws2.Range(ws2.Cells(iLss2.Row, 2), ws2.Cells(iLss2.Row, jF))
  38. For Each cell In emptyrange
  39. If IsEmpty(cell) Then
  40. AddCheckMark cell
  41. End If
  42. Next cell
  43. Else
  44. End If
  45.  
  46. End If
  47. End If
  48. Next id
  49.  
  50. 'Sort signal list in alphabetical order (in measurement database sheet)
  51. SrtRowF = ws2.Cells(Rows.Count, 1).End(xlUp).Row
  52. SrtColF = ws2.Cells(7, Columns.Count).End(xlToLeft).Column
  53. Set SrtRngF = ws2.Range(ws2.Cells(7, 1), ws2.Cells(SrtRowF, SrtColF))
  54. Set AcellF = ws2.Range("A7")
  55. SrtRngF.Sort key1:=AcellF, order1:=xlAscending, Header:=xlYes
  56.  
  57.  
  58. iL = ws2.Range("A" & Rows.Count).End(xlUp).Row
  59.  
  60. ws2.Columns(jFn).AutoFit
  61.  
  62.  
  63. 'Run comparison of each measurement file one by one
  64. Set rng2 = ws1.Range(ws1.Cells(7, j), ws1.Cells(Rows.Count, j).End(xlUp))
  65. For i = 8 To iL
  66. Set rng1 = ws2.Cells(i, 1)
  67. Set rng5 = ws2.Cells(i, jFn)
  68. Set var = rng2.Find(rng1.Value, LookIn:=xlValues, LookAt:=xlWhole)
  69. If Not var Is Nothing Then
  70. AddFailMark rng5
  71. Else
  72. AddCheckMark rng5
  73. End If
  74. Next i
  75. Next j
  76.  
  77. 'Cleanup final sheet
  78. ClnRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row
  79. Clncol = ws2.Cells(7, Columns.Count).End(xlToLeft).Column
  80.  
  81. Set Clnup = ws2.Range(ws2.Cells(7, 2), ws2.Cells(ClnRow, Clncol))
  82. Clnup.VerticalAlignment = xlCenter
  83. Clnup.HorizontalAlignment = xlCenter
  84.  
  85.  
  86. 'Clear draft sheet
  87. lRows0 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
  88. lCols0 = ws1.Cells(7, Columns.Count).Column
  89. Set rng = ws1.Range(ws1.Cells(7, 2), ws1.Cells(lRows0, lCols0))
  90. rng.ClearContents
  91. ws2.Rows(7).Font.Name = "Calibri"
  92.  
  93. Call Meas_Info
  94.  
  95. ErrHandler:
  96. Sheet1.Protect Password:="abc"
  97. Sheet2.Protect Password:="abc"
  98. Sheet3.Protect Password:="abc"
  99. Sheet5.Protect Password:="abc"
  100. Sheet6.Protect Password:="abc"
  101. Sheet7.Protect Password:="abc"
  102. Sheet8.Protect Password:="abc"
  103. Sheet5.Visible = xlSheetVeryHidden
  104. Sheet6.Visible = xlSheetVeryHidden
  105. Sheet7.Visible = xlSheetVeryHidden
  106. Sheet8.Visible = xlSheetVeryHidden
  107.  
  108. If Err.Number <> 0 Then
  109. Msg = "Error # " & Str(Err.Number) & " was generated by " _
  110. & Err.Source & Chr(13) & Err.Description
  111. MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
  112. End If
  113. Exit Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement