?
Solved

find method vba

Posted on 2014-03-28
6
Medium Priority
?
249 Views
Last Modified: 2014-03-28
I have the following code

If IsError(Range(Cells(y, "A"), Cells(y, x - 1)).Find(what:=Cells(y, x).Value)) Then

Else
GoTo INI
End If

I want to identify when range.find not found nothing, but always  go to the else :goto INI, any idea?
0
Comment
Question by:joyacv2
  • 3
  • 3
6 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 39962383
I'd do something like this

on error resume next

set rgFound=Range(Cells(y, "A"), Cells(y, x - 1)).Find(what:=Cells(y, x).Value)) 

if err<>0 then
msgbox "error happened"
 err.clear
else
msgbox "all is well"
end if

on error goto 0

Open in new window

0
 
LVL 1

Author Comment

by:joyacv2
ID: 39962428
Hi,

where is the goto ini statement?
0
 
LVL 39

Expert Comment

by:nutsch
ID: 39962461
Put it in the else part
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:joyacv2
ID: 39962473
Hi,

not working, enters in a infinite loop. Any other idea?

there is a loop running, i need to verify in the cells before the cells(y,x) before to enter the number, ensuring the number doesn't repeat
0
 
LVL 39

Accepted Solution

by:
nutsch earned 2000 total points
ID: 39962490
You can use application.worksheetfunction.countif(range, value) >0 to check if its already been entered rather than do a range.find
0
 
LVL 1

Author Closing Comment

by:joyacv2
ID: 39962499
the perfect solution!!!!! Thanks!!!!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question