| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A simple if < nn then Msgbox "No Match"
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
A simple if < nn then Msgbox "No Match"
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
A simple if < nn then Msgbox "No Match"
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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
A simple if < nn then Msgbox "No Match"
On Thursday, December 12, 2013 11:19:20 PM UTC-8, Claus Busch wrote:
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. -- 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
A simple if < nn then Msgbox "No Match"
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. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
A simple if < nn then Msgbox "No Match"
On Friday, December 13, 2013 12:50:55 AM UTC-8, Claus Busch wrote:
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. -- 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| MsgBox "Choose one ", vbYesNoCancel, " Three Options. " | Excel Programming | |||
| Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
| change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
| How to replace "#N/A" w "0"when vlookup couldn't find the match? | Excel Discussion (Misc queries) | |||
| Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Linear Mode
