A simple if <> nn then Msgbox "No Match"

  • Thread starter Thread starter Howard
  • Start date Start date
H

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
 
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
Click to expand...


Regards
Claus B.
 
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.
Click to expand...

try:

Sub PlugedIn2()
Dim FindCable As String
Dim ACM As Range
Dim c As Range
Dim lr As Long
Dim i As Integer
Dim firstaddress As String

With Sheets("Find Cables")
For i = 3 To 13 Step 2
FindCable = FindCable & .Range("C" & i) & " "
Next
End With

FindCable = Trim(FindCable)

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

If WorksheetFunction.CountIf(ACM, FindCable) = 0 Then
MsgBox "No Match"
Exit Sub
End If

With Sheets("Find Cables")
Set c = ACM.Find(FindCable, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
.Range("A" & Rows.Count).End(xlUp)(2) = c.Offset(, -5)
.Range("B" & Rows.Count).End(xlUp)(2) = c.Offset(, -4)
.Range("C" & Rows.Count).End(xlUp)(2) = c.Offset(, -3)
.Range("D" & Rows.Count).End(xlUp)(2) = c.Offset(, -2)
.Range("E" & Rows.Count).End(xlUp)(2) = c.Offset(, 9)
.Range("F" & Rows.Count).End(xlUp)(2) = c.Offset(, 10)
.Range("G" & Rows.Count).End(xlUp)(2) = c.Offset(, 12)

Set c = ACM.FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With

End Sub


Regards
Claus B.
 
Hi Howard,



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







try:



Sub PlugedIn2()

Dim FindCable As String

Dim ACM As Range

Dim c As Range

Dim lr As Long

Dim i As Integer

Dim firstaddress As String



With Sheets("Find Cables")

For i = 3 To 13 Step 2

FindCable = FindCable & .Range("C" & i) & " "

Next

End With



FindCable = Trim(FindCable)



With Sheets("all cables")

lr = .Cells(.Rows.Count, 6).End(xlUp).Row

Set ACM = .Range("F2:F" & lr)

End With



If WorksheetFunction.CountIf(ACM, FindCable) = 0 Then

MsgBox "No Match"

Exit Sub

End If



With Sheets("Find Cables")

Set c = ACM.Find(FindCable, LookIn:=xlValues)

If Not c Is Nothing Then

firstaddress = c.Address

Do

.Range("A" & Rows.Count).End(xlUp)(2) = c.Offset(, -5)

.Range("B" & Rows.Count).End(xlUp)(2) = c.Offset(, -4)

.Range("C" & Rows.Count).End(xlUp)(2) = c.Offset(, -3)

.Range("D" & Rows.Count).End(xlUp)(2) = c.Offset(, -2)

.Range("E" & Rows.Count).End(xlUp)(2) = c.Offset(, 9)

.Range("F" & Rows.Count).End(xlUp)(2) = c.Offset(, 10)

.Range("G" & Rows.Count).End(xlUp)(2) = c.Offset(, 12)



Set c = ACM.FindNext(c)

Loop While Not c Is Nothing And c.Address <> firstaddress

End If

End With



End Sub





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2
Click to expand...


Wow! Sure works nicely.

I have to school myself on firstaddress = c.Address, I see firstaddress used a lot.

And using "Find" instead of the ole' For Each c In is something I need to work on also.

I do have a long way to go, for sure.

Thanks Claus.

Regards,
Howard
 
Hi Howard,

Am Fri, 13 Dec 2013 00:44:51 -0800 (PST) schrieb Howard:
And using "Find" instead of the ole' For Each c In is something I need to work on also.
Click to expand...

Find is much faster than looping through the range.

You have to check the values in the columns. In your post you copied
Offset(, -5) two times, one time to column A and one time to B:
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

I don't know if changing is correct.


Regards
Claus B.
 
Hi Howard,



Am Fri, 13 Dec 2013 00:44:51 -0800 (PST) schrieb Howard:






Find is much faster than looping through the range.



You have to check the values in the columns. In your post you copied

Offset(, -5) two times, one time to column A and one time to B:

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



I don't know if changing is correct.





Regards

Claus B.

--
Click to expand...


Yes, I had some typos in the code, although it worked okay the test data was just that, test data, but the code was working.

I cleaned that up on your code suggestion and it works very smooth.

Thanks again.

Howard
 
Back
Top