Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Sub AddSignals()
- 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
- 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
- Dim Clnup As Range, Clncol As Long, ClnRow As Long, trimrng As Range, trimrng1 As Range, emptyrange As Range, EmptyCol As Long
- On Error GoTo ErrHandler
- Set ws1 = Sheet7
- Set ws2 = Sheet2
- 'Look for new signals and add to signal list
- jL = ws1.Cells(7, Columns.Count).End(xlToLeft).Column
- For j = 3 To jL
- 'Set range limits
- jF = ws2.Cells(7, Columns.Count).End(xlToLeft).Column
- jFn = jF + 1
- ws2.Cells(7, jFn).Value = ws1.Cells(7, j).Value
- idL = ws1.Cells(Rows.Count, j).End(xlUp).Row
- For id = 8 To idL
- iLs2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
- Set iLss2 = ws2.Cells(iLs2 + 1, 1)
- Set rng1s = ws1.Cells(id, j)
- If Not IsEmpty(rng1s) Then
- Set rng2s = ws2.Range(ws2.Cells(7, 1), ws2.Cells(ws2.Cells(Rows.Count, 1).End(xlUp).Row, 1))
- Set var2 = rng2s.Find(rng1s.Value, LookIn:=xlValues, LookAt:=xlWhole)
- If var2 Is Nothing Then
- bln = True
- If bln = True Then
- iLss2.Value = rng1s.Value
- 'remove any spaces from cells
- iLss2.Value = WorksheetFunction.Trim(iLss2.Value)
- Set emptyrange = ws2.Range(ws2.Cells(iLss2.Row, 2), ws2.Cells(iLss2.Row, jF))
- For Each cell In emptyrange
- If IsEmpty(cell) Then
- AddCheckMark cell
- End If
- Next cell
- Else
- End If
- End If
- End If
- Next id
- 'Sort signal list in alphabetical order (in measurement database sheet)
- SrtRowF = ws2.Cells(Rows.Count, 1).End(xlUp).Row
- SrtColF = ws2.Cells(7, Columns.Count).End(xlToLeft).Column
- Set SrtRngF = ws2.Range(ws2.Cells(7, 1), ws2.Cells(SrtRowF, SrtColF))
- Set AcellF = ws2.Range("A7")
- SrtRngF.Sort key1:=AcellF, order1:=xlAscending, Header:=xlYes
- iL = ws2.Range("A" & Rows.Count).End(xlUp).Row
- ws2.Columns(jFn).AutoFit
- 'Run comparison of each measurement file one by one
- Set rng2 = ws1.Range(ws1.Cells(7, j), ws1.Cells(Rows.Count, j).End(xlUp))
- For i = 8 To iL
- Set rng1 = ws2.Cells(i, 1)
- Set rng5 = ws2.Cells(i, jFn)
- Set var = rng2.Find(rng1.Value, LookIn:=xlValues, LookAt:=xlWhole)
- If Not var Is Nothing Then
- AddFailMark rng5
- Else
- AddCheckMark rng5
- End If
- Next i
- Next j
- 'Cleanup final sheet
- ClnRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row
- Clncol = ws2.Cells(7, Columns.Count).End(xlToLeft).Column
- Set Clnup = ws2.Range(ws2.Cells(7, 2), ws2.Cells(ClnRow, Clncol))
- Clnup.VerticalAlignment = xlCenter
- Clnup.HorizontalAlignment = xlCenter
- 'Clear draft sheet
- lRows0 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
- lCols0 = ws1.Cells(7, Columns.Count).Column
- Set rng = ws1.Range(ws1.Cells(7, 2), ws1.Cells(lRows0, lCols0))
- rng.ClearContents
- ws2.Rows(7).Font.Name = "Calibri"
- Call Meas_Info
- ErrHandler:
- Sheet1.Protect Password:="abc"
- Sheet2.Protect Password:="abc"
- Sheet3.Protect Password:="abc"
- Sheet5.Protect Password:="abc"
- Sheet6.Protect Password:="abc"
- Sheet7.Protect Password:="abc"
- Sheet8.Protect Password:="abc"
- Sheet5.Visible = xlSheetVeryHidden
- Sheet6.Visible = xlSheetVeryHidden
- Sheet7.Visible = xlSheetVeryHidden
- Sheet8.Visible = xlSheetVeryHidden
- If Err.Number <> 0 Then
- Msg = "Error # " & Str(Err.Number) & " was generated by " _
- & Err.Source & Chr(13) & Err.Description
- MsgBox Msg, , "Error", Err.HelpFile, Err.HelpContext
- End If
- Exit Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement