Hi Howard,

Am Thu, 12 Dec 2013 19:45:57 -0800 (PST) schrieb Howard:

Trying to get a message to display if no match is found in this
For Each c In ACM.

I have set FindCable to simply "Donkey" then put an x behind it in the range ACM to test the msgbox alert.

Once I put the code in for the Msgbox it shows "No Match" every time I run the macro, even if Donkey is providing a true match.

What gives?

I have similar examples in my cheat sheet but none have helped me here.

Thanks.
Howard

Option Explicit

Sub PlugedIn()
Dim FindCable As String
Dim ACM As Range
Dim c As Range
Dim lr As Long

With Sheets("Find Cables")

FindCable = "Donkey" 'Range("C3") & " " & Range("C5") & " " & _
Range("C7") & " " & Range("C9") & " " & _
Range("C11") & " " & Range("C13")
End With

lr = Cells(Rows.Count, 6).End(xlUp).Row
Set ACM = Sheets("all cables").Range("F2:F" & lr)

For Each c In ACM

If c = FindCable Then
c.Offset(, -5).Copy
Sheets("Find Cables").Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

c.Offset(, -5).Copy
Sheets("Find Cables").Range("B" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

c.Offset(, -4).Copy
Sheets("Find Cables").Range("C" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

c.Offset(, -2).Copy
Sheets("Find Cables").Range("D" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

c.Offset(, 9).Copy
Sheets("Find Cables").Range("E" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

c.Offset(, 10).Copy
Sheets("Find Cables").Range("F" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

c.Offset(, 12).Copy
Sheets("Find Cables").Range("G" & Rows.Count).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

ElseIf c < FindCable Then
MsgBox "No Match"
Exit Sub
End If

Next
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2