Compare range to named range for ANY match

[Phil Smith]

Hi all.

I have a named range "Stations".

I wish to compare the range (B14:B & final row with any data in) on each of more than 100 worksheets with "Stations" to see if there is ANY match.

If there is >=1 match then ....

...

Else

...

End If

I dont know whether the best way to do this is via VBA or via a formula in the cell and regardless of which method, I am clueless.

Your help is appreciated.

 

[Phil Smith]

If this is of any help towards describing the problem...

FinalRow = Row that contains "End" on worksheet

If FinalRow=16 then

Job="Cover"

ElseIf FinalRow>16 AND any of the stations in "Stations" are in the range B14:B&FinalRow

then Job="Running"

ElseIf FinalRow>16 AND none of the stations in "Stations" are in the range B14:B&FinalRow

then Job="Depot"

End If

 

[DanteAmor]

Try this macro

Sub Compare_Fange()
    'Compare range to named range for ANY match
    'varios 25ene2019
    '
    Dim cMatch As Boolean
    Dim rango As Range, valor As Range
    Dim h As Worksheet
    Dim cad As String
    '
    Application.StatusBar = False
    Set rango = Range("Stations")
    cMatch = False
    For Each valor In rango
        For Each h In Sheets
            Application.StatusBar = "Compare value : " & valor.Value & " In sheet : " & h.Index
            If h.Name <> rango.Worksheet.Name Then
                Set b = h.Columns("B").Find(valor.Value, lookat:=xlWhole, LookIn:=xlValues)
                If Not b Is Nothing Then
                    cMatch = True
                    cad = "Value : " & valor.Value & vbCr & "Found in cell : " & b.Address & " sheet : " & h.Name
                    Exit For
                End If
            End If
        Next
        If cMatch Then Exit For
    Next
    If cMatch Then
        MsgBox cad
    Else
        MsgBox "No match"
    End If
    Application.StatusBar = False
End Sub

 

[Phil Smith]

Thanks for your quick reply Dante.

Could you possibly rework it for dealing with one worksheet at a time, Sheets("MB" & NewDiagram) and using the following basic template...? BTW, NewDiagram increases through each iteration of a loop.

FinalRow = the row that contains "End" on worksheet

If FinalRow=16 then

Job="Cover"

ElseIf FinalRow>16 AND any of the stations in "Stations" are in the range B14:B&FinalRow

then Job="Running"

ElseIf FinalRow>16 AND none of the stations in "Stations" are in the range B14:B&FinalRow

then Job="Depot"

End If
------

Sorry for not getting my head around your code!

 

[DanteAmor]

I do not understand about FinalRow.



To understand Let's work with only 2 sheets.
On which sheet is the "Stations" range?
On which sheet is the word "End" and in which column?
Which sheet will I look for?

you could upload a copy of your file only with 2 sheets to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[Phil Smith]

I tried to send you a private message with the link but was unable to do so.

 

[Phil Smith]

After much headbanging and lots of reworded web searches, I have come up with the following formula, only for it to produce an #N/A error.

=IF(MATCH("End",A:A)=16,"COVER",IF(SUMPRODUCT(--(B13:B100=Stations))>0,"RUNNING","DEPOT"))

The first part, IF(MATCH("End",A:A)=16 works absolutely fine.

The last part is where it throws up an error, IF(SUMPRODUCT(--(B13:B100=Stations))>0,"RUNNING","DEPOT"))

I couldn't work out how to reference a dynamic range for column B using MATCH("End",A:A) to find the final row. Therefore I have used a static range of B13:B100 and want to compare that range to my named range, "Stations".

If there is ANY match at all between the data in B13:B100 and the named range "Stations", then the cell should show "RUNNING", otherwise "DEPOT".

Can anyone enlighten me to what is causing the error and how to rectify it?

 

[Phil Smith]

Answered! Please see https://www.mrexcel.com/forum/excel-questions/1085241-help-requested-sumproduct-compare-ranges.html