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


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
 
Ad

Advertisements

C

Claus Busch

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.
 
C

Claus Busch

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.

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.
 
H

Howard

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


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
 
C

Claus Busch

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.

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.
 
Ad

Advertisements

H

Howard

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.

--


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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top