Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SPOT THE MISTAKE!....
CAN ANYONE SEE WHERE THE ERROR IS WITH THE FOLLOWING CODE. I REGULARLY
HAVE PROBLEMS WITH THE FIND METHOD. MAYBE I'M MISSING AN ARGUMENT OUT, AS IT ALWAYS THROWS THE ERROR "OBJECT REQUIRED" EVEN WHEN THE SERCHSTRING IS IN THE AREA BEING SEARCHED MAYBE THE PROBLEM IS THAT THE SEARCH STRINGS AND SEARCHAREA ARE ON DIFFERENT WORKSHEETS? ANY HELP GREATLY APPRECIATED, JASON. Dim myFoundAddress As String For i = 2 To x On Error Resume Next myFoundAddress = Sheets("Static").Range("MG_Tournaments").Find _ (What:=Sheets("Input").Cells(i, 7), LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, _ SearchFormat:=False).Address If myFoundAddress < "" Then Cells(i, 8) = "True" Else Cells(i, 8) = "False" End If Next On Error GoTo 0 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SPOT THE MISTAKE!....
Dim myFoundAddress As String For i = 2 To x On Error Resume Next myFoundAddress = Sheets("Static").Range("MG_Tournaments").Find _ (What:=Sheets("Input").Cells(i, 7), LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, _ SearchFormat:=False).Address If myFoundAddress < "" Then Sheets("Static").Cells(i, 8) = "True" '<---- add the sheets object Else Sheets("Static").Cells(i, 8) = "False" '<---- here too End If Next On Error GoTo 0 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SPOT THE MISTAKE!....
Stop using the "on error resume next" stuff.
Instead set a range variable to that found cell, then check to see if that range variable is nothing (not found). Dim myFoundCell As Range For i = 2 To x myFoundCell = Sheets("Static").Range("MG_Tournaments").Find _ (What:=Sheets("Input").Cells(i, 7), LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True, _ SearchFormat:=False) if myfoundcell is nothing then cells(i,8).value = True else cells(i,8).value = false end if next i ps. I think it's more difficult to read when you post in all caps. WhytheQ wrote: CAN ANYONE SEE WHERE THE ERROR IS WITH THE FOLLOWING CODE. I REGULARLY HAVE PROBLEMS WITH THE FIND METHOD. MAYBE I'M MISSING AN ARGUMENT OUT, AS IT ALWAYS THROWS THE ERROR "OBJECT REQUIRED" EVEN WHEN THE SERCHSTRING IS IN THE AREA BEING SEARCHED MAYBE THE PROBLEM IS THAT THE SEARCH STRINGS AND SEARCHAREA ARE ON DIFFERENT WORKSHEETS? ANY HELP GREATLY APPRECIATED, JASON. Dim myFoundAddress As String For i = 2 To x On Error Resume Next myFoundAddress = Sheets("Static").Range("MG_Tournaments").Find _ (What:=Sheets("Input").Cells(i, 7), LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, _ SearchFormat:=False).Address If myFoundAddress < "" Then Cells(i, 8) = "True" Else Cells(i, 8) = "False" End If Next On Error GoTo 0 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
SPOT THE MISTAKE!....
Correct, only one thing I guess - Set myFoundCell = .... instead of just
myFoundCell = .... because Range is an object. "Dave Peterson" schreef in bericht ... Stop using the "on error resume next" stuff. Instead set a range variable to that found cell, then check to see if that range variable is nothing (not found). Dim myFoundCell As Range For i = 2 To x myFoundCell = Sheets("Static").Range("MG_Tournaments").Find _ (What:=Sheets("Input").Cells(i, 7), LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True, _ SearchFormat:=False) if myfoundcell is nothing then cells(i,8).value = True else cells(i,8).value = false end if next i ps. I think it's more difficult to read when you post in all caps. WhytheQ wrote: CAN ANYONE SEE WHERE THE ERROR IS WITH THE FOLLOWING CODE. I REGULARLY HAVE PROBLEMS WITH THE FIND METHOD. MAYBE I'M MISSING AN ARGUMENT OUT, AS IT ALWAYS THROWS THE ERROR "OBJECT REQUIRED" EVEN WHEN THE SERCHSTRING IS IN THE AREA BEING SEARCHED MAYBE THE PROBLEM IS THAT THE SEARCH STRINGS AND SEARCHAREA ARE ON DIFFERENT WORKSHEETS? ANY HELP GREATLY APPRECIATED, JASON. Dim myFoundAddress As String For i = 2 To x On Error Resume Next myFoundAddress = Sheets("Static").Range("MG_Tournaments").Find _ (What:=Sheets("Input").Cells(i, 7), LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, _ SearchFormat:=False).Address If myFoundAddress < "" Then Cells(i, 8) = "True" Else Cells(i, 8) = "False" End If Next On Error GoTo 0 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
SPOT THE MISTAKE!....
Yep. I omitted it so you could...
SPOT THE MISTAKE! (Or maybe I just forgot to add it when I was composing the message <bg.) Glad you got it working even with the error. moon wrote: Correct, only one thing I guess - Set myFoundCell = .... instead of just myFoundCell = .... because Range is an object. "Dave Peterson" schreef in bericht ... Stop using the "on error resume next" stuff. Instead set a range variable to that found cell, then check to see if that range variable is nothing (not found). Dim myFoundCell As Range For i = 2 To x myFoundCell = Sheets("Static").Range("MG_Tournaments").Find _ (What:=Sheets("Input").Cells(i, 7), LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=True, _ SearchFormat:=False) if myfoundcell is nothing then cells(i,8).value = True else cells(i,8).value = false end if next i ps. I think it's more difficult to read when you post in all caps. WhytheQ wrote: CAN ANYONE SEE WHERE THE ERROR IS WITH THE FOLLOWING CODE. I REGULARLY HAVE PROBLEMS WITH THE FIND METHOD. MAYBE I'M MISSING AN ARGUMENT OUT, AS IT ALWAYS THROWS THE ERROR "OBJECT REQUIRED" EVEN WHEN THE SERCHSTRING IS IN THE AREA BEING SEARCHED MAYBE THE PROBLEM IS THAT THE SEARCH STRINGS AND SEARCHAREA ARE ON DIFFERENT WORKSHEETS? ANY HELP GREATLY APPRECIATED, JASON. Dim myFoundAddress As String For i = 2 To x On Error Resume Next myFoundAddress = Sheets("Static").Range("MG_Tournaments").Find _ (What:=Sheets("Input").Cells(i, 7), LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, _ SearchFormat:=False).Address If myFoundAddress < "" Then Cells(i, 8) = "True" Else Cells(i, 8) = "False" End If Next On Error GoTo 0 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
SPOT THE MISTAKE!....
Sorry Don but I'm not prepared to accept any sort of criticism of the
case I use. I wasn't shouting, and if you look back on my previous posts on this group (and there are a lot) then you will see that I'm always polite. I have been involved in threads previously where people have posted really rude comments, in lower case text - maybe you should concentrate on those! Thanks for your comments & long may the internet be free of petty red-tape. Jason Don Guillett wrote: 1. Please don't SHOUT. It is considered rude to type in all caps. 2. Have a look in the vba help index for FINDNEXT. There is a good example. -- Don Guillett SalesAid Software "WhytheQ" wrote in message oups.com... CAN ANYONE SEE WHERE THE ERROR IS WITH THE FOLLOWING CODE. I REGULARLY HAVE PROBLEMS WITH THE FIND METHOD. MAYBE I'M MISSING AN ARGUMENT OUT, AS IT ALWAYS THROWS THE ERROR "OBJECT REQUIRED" EVEN WHEN THE SERCHSTRING IS IN THE AREA BEING SEARCHED MAYBE THE PROBLEM IS THAT THE SEARCH STRINGS AND SEARCHAREA ARE ON DIFFERENT WORKSHEETS? ANY HELP GREATLY APPRECIATED, JASON. Dim myFoundAddress As String For i = 2 To x On Error Resume Next myFoundAddress = Sheets("Static").Range("MG_Tournaments").Find _ (What:=Sheets("Input").Cells(i, 7), LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, _ SearchFormat:=False).Address If myFoundAddress < "" Then Cells(i, 8) = "True" Else Cells(i, 8) = "False" End If Next On Error GoTo 0 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
SPOT THE MISTAKE!....
Thanks for the help Dave!
WhytheQ wrote: Sorry Don but I'm not prepared to accept any sort of criticism of the case I use. I wasn't shouting, and if you look back on my previous posts on this group (and there are a lot) then you will see that I'm always polite. I have been involved in threads previously where people have posted really rude comments, in lower case text - maybe you should concentrate on those! Thanks for your comments & long may the internet be free of petty red-tape. Jason Don Guillett wrote: 1. Please don't SHOUT. It is considered rude to type in all caps. 2. Have a look in the vba help index for FINDNEXT. There is a good example. -- Don Guillett SalesAid Software "WhytheQ" wrote in message oups.com... CAN ANYONE SEE WHERE THE ERROR IS WITH THE FOLLOWING CODE. I REGULARLY HAVE PROBLEMS WITH THE FIND METHOD. MAYBE I'M MISSING AN ARGUMENT OUT, AS IT ALWAYS THROWS THE ERROR "OBJECT REQUIRED" EVEN WHEN THE SERCHSTRING IS IN THE AREA BEING SEARCHED MAYBE THE PROBLEM IS THAT THE SEARCH STRINGS AND SEARCHAREA ARE ON DIFFERENT WORKSHEETS? ANY HELP GREATLY APPRECIATED, JASON. Dim myFoundAddress As String For i = 2 To x On Error Resume Next myFoundAddress = Sheets("Static").Range("MG_Tournaments").Find _ (What:=Sheets("Input").Cells(i, 7), LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, _ SearchFormat:=False).Address If myFoundAddress < "" Then Cells(i, 8) = "True" Else Cells(i, 8) = "False" End If Next On Error GoTo 0 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
SPOT THE MISTAKE!....
There is a saying, "When in Rome, do as the Romans do". All caps IS
considered shouting and rude. I was trying to help you to get more responses to your questions. I'm glad that, after this post, that you will no longer need my help. BTW. Dave also cautioned you about it. I think it's more difficult to read when you post in all caps. Have it your own way. ========= Example I cited With Worksheets(1).Range("a1:a500") Set c = .Find(2, lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Value = 5 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With -- Don Guillett SalesAid Software "WhytheQ" wrote in message oups.com... Sorry Don but I'm not prepared to accept any sort of criticism of the case I use. I wasn't shouting, and if you look back on my previous posts on this group (and there are a lot) then you will see that I'm always polite. I have been involved in threads previously where people have posted really rude comments, in lower case text - maybe you should concentrate on those! Thanks for your comments & long may the internet be free of petty red-tape. Jason Don Guillett wrote: 1. Please don't SHOUT. It is considered rude to type in all caps. 2. Have a look in the vba help index for FINDNEXT. There is a good example. -- Don Guillett SalesAid Software "WhytheQ" wrote in message oups.com... CAN ANYONE SEE WHERE THE ERROR IS WITH THE FOLLOWING CODE. I REGULARLY HAVE PROBLEMS WITH THE FIND METHOD. MAYBE I'M MISSING AN ARGUMENT OUT, AS IT ALWAYS THROWS THE ERROR "OBJECT REQUIRED" EVEN WHEN THE SERCHSTRING IS IN THE AREA BEING SEARCHED MAYBE THE PROBLEM IS THAT THE SEARCH STRINGS AND SEARCHAREA ARE ON DIFFERENT WORKSHEETS? ANY HELP GREATLY APPRECIATED, JASON. Dim myFoundAddress As String For i = 2 To x On Error Resume Next myFoundAddress = Sheets("Static").Range("MG_Tournaments").Find _ (What:=Sheets("Input").Cells(i, 7), LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, _ SearchFormat:=False).Address If myFoundAddress < "" Then Cells(i, 8) = "True" Else Cells(i, 8) = "False" End If Next On Error GoTo 0 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
spot the mistake in formula | Excel Discussion (Misc queries) | |||
X marks the spot | Excel Discussion (Misc queries) | |||
Hosur an IT hot spot? | Excel Worksheet Functions | |||
X marks the spot | Excel Programming | |||
I want a name in one spot and it's code in another | Excel Discussion (Misc queries) |