Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

find method vba

Posted on 2014-03-28
6
Medium Priority
?
247 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

722 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