Below is my snippet of code. When I run the routine it doesn't match the entire cell contents, it will find partial matches. You'll also notice in the code that some of the parameters (including LookAt) did not auto-capitalize. I'm not getting an error message, but the Find method just isn't working the way I'm needing any to. Any advice?
Please Login or Register to view this content.
Have you considered using Application.Match?
By the way, the arguments of a method don't autocapitalze.Please Login or Register to view this content.
If posting code please use code tags, see here.
Without seeing a worksheet I couldn't say, but the not capitalizing doesn't mean anything. I usually capitalize mine because I have noticed sometimes it doesn't and I'm just picky.
I wrote the code below and didn't capitalize lookin or lookat and as you can see LookIn changed but lookat didn't. The code works just fine.
If you don't want to take Norie's suggestion then post a workbook.
ViewPic
EDIT: Very strange, I wrote some other code and didn't capitalize anything, but again lookin changed itself to LookIn but everything else stayed lower case.Please Login or Register to view this content.
Last edited by skywriter; 01-07-2016 at 05:15 PM.
Thanks for the reply Norie. I haven't used Application.Match before. I'm kind of following along with what you have there, but not entirely. My purpose for this section of my code is a validation check to make sure the user doesn't input duplicate data. So, if Me.CourseText.Value already exists in column B the user will get a message box asking if they want to overwrite the existing data in the row that the course exists. With all that said, help me understand what the variable Res actually represents in your code. Also, am I correct that in your code the variable rFound would be the address that a match was found?
Even with Norie's example I would still like to determine what is causing the problem with the Find method in case I run into it in the future. I've attached my workbook. I appreciate the assistance.
In the code I posted if the value in Me.CourseText is found in column 2 Application.Match will return the row number where it's found, if it's not found then it will return an error.
If a match is found the variable rFound would be set to refer to the cell the match was found in.
I included it because I wasn't sure what you were going to do with rFound in the rest of your code, if you are only trying to check for duplicates you don't really need it.
Just noticed there's a problem with your code.
In the If statement if rFound is Nothing rFound.Offset(0,-1).Value will cause an error and since you have On Error Resume Next execution will skip to the message box, so the Find might not be the problem.
Please Login or Register to view this content.
Ok. I think I'm following along. So taking your code to the next step that I'd need it for, if a match is found I can have a message box pop up asking if the user wants to overwrite the existing row. If they select yes I can us the Res variable as the row number and start doing a .Cells(Res,1).Value = Me.TextBox.Value, .Cells(Res,2).Value = Me.Textbox.Value, etc. etc., correct?
Edit: By the way, I'm not asking you to write the code for me, I'm just getting confirmation before I dive in.
Yes, you can use Res for the row to write the data to.
Just out of curiosity, in the file you attached you had testing twice in column B.
So if you were trying to see if Brand B and testing is a duplicate, then wouldn't you find the testing in B3 and your code would look at A3 see Brand A and therefore not see Testing and Brand B as a duplicate, but it does exist in the row below?
I admit I haven't studied all your code so maybe it's in there somewhere.
Ahh haa! I believe you found the problem Norie. My intent of the On Error Resume Next was to skip the error when no duplicate was found (rFound = Nothing). I overlooked that it would skip to the msgbox.
The Application.Match is definitely the route to go. I appreciate your help.
skywriter: Go point, I wasn't thinking that the Find method or Application.Match would stop after the first duplicate in column B is found regardless if column A is a duplicate as well. I'm thinking now that a loop would probably be the better approach for me. This has been a prime example of me thinking I knew more than I actually do.
I really appreciate both yours and Norie's help on this. You both have educated me not only on actual VBA coding but on really thinking out the process. I'm going to mark the thread as Solved since the issue described in the original request has been resolved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks