Solved

find method vba

Posted on 2014-03-28
6
240 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
Comment Utility
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
Comment Utility
Hi,

where is the goto ini statement?
0
 
LVL 39

Expert Comment

by:nutsch
Comment Utility
Put it in the else part
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:joyacv2
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
the perfect solution!!!!! Thanks!!!!
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now