Use VBA to compare two lists on two sheets and report missing items on new, third sheet.

[RLloyd]

I could use some help validating weekly system analysis.

Every week I receive a network report of all devices in my IS. Sometimes the scan doesn’t report all of them and I need to know which ones are missing from the report. I need the VBA script because the report runs weekly.

Column B in sheet “Master” contains my list of all servers in the IS. Column F in sheet “Scan” contains the results of the weekly report. Each sheet has a header row. The script needs to create a new sheet in the workbook and create a list of the missing network devices in column A.

As always, I appreciate the help.

 

[mrshl9898]

Sub findmissing()


Dim rownum1 As Long
Dim rownum2 As Long
Dim searchfor As String
Dim Rng As Range


    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets.Add(After:= _
             ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = "missing"
    
rownum2 = 1
rownum1 = 2


Do Until Sheets("Master").Cells(rownum1, 2).Value = ""


searchfor = Sheets("Master").Cells(rownum1, 2).Value
    With Sheets("Scan").Range("F:F")
        Set Rng = .Find(What:=searchfor, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            GoTo foundit
        Else
            Sheets("missing").Cells(rownum2, 1).Value = searchfor
            rownum2 = rownum2 + 1
        End If
    End With


foundit:


rownum1 = rownum1 + 1
Loop
    
End Sub

 

[RobertSF]

I came up with this. I tested it, and it works, but I'm kind of new to VBA so it may not be the best approach.

Sub CompareScanToMaster()    Dim ndx As Long
    Dim rowsMaster As Long
    Dim rowsScan As Long
    Dim rngMaster As Range
    Dim rngScan As Range
    Dim numCount As Long
    Dim rng As Range
    
    rowsMaster = ActiveWorkbook.Sheets("Master").UsedRange.rows.Count
    Set rngMaster = ActiveWorkbook.Sheets("Master").Range("B1:B" & rowsMaster)
    
    rowsScan = ActiveWorkbook.Sheets("Scan").UsedRange.rows.Count
    Set rngScan = ActiveWorkbook.Sheets("Scan").Range("F1:F" & rowsScan)
    
    ActiveWorkbook.Sheets.Add.Name = "Missing"
    ActiveWorkbook.Sheets("Missing").Range("A1") = "Missing"
    numCount = 2
    
    For ndx = 2 To rowsMaster
        Set rng = rngScan.Find( _
            What:=rngMaster(ndx).Text, _
            LookIn:=xlValues, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False)
        If rng Is Nothing Then
            ActiveWorkbook.Sheets("Missing").Cells(numCount, 1) = rngMaster(ndx)
            numCount = numCount + 1
        End If
    Next ndx
End Sub

 

[RLloyd]

Many thanks for the code, but it stops, and the debug function highlights ws.Name = "missing" and the new sheet is not created. Can you suggest an alternative statement?

 

[RLloyd]

Thanks for the code! I will try it tomorrow morning and apprise.

 

[RobertSF]

You're welcome! By the way, I think you might have a typo in your code because I tried the other code that contains ws.Name = "missing" and it ran fine.