Solved

Excel VBA FindNext Error

Posted on 2007-03-23
2
2,330 Views
Last Modified: 2013-12-27
Hi

I am using the following VBA code to find all instances of a value in a spreadsheet.
The first Find works but the code falls over on the FindNext part that I have marked with #####

    Dim c As Range
    Dim firstAddress As String
    With Worksheets(oSheetName)
     
        Set c = Cells.Find(What:=oWhat, After:=Range("A1"), LookIn:=oLookin, LookAt:= _
        oLookat, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)
        Call ActionCellContent3(c.Address)
       
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                Set c = .FindNext(c) '########
   
                Call ActionCellContent3(c.Address)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
0
Comment
Question by:murbro
2 Comments
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 18849469
Hi

'Mr Microsoft' recommends replacing the findnext with a duplicate of the find command BUT adding to it the "after:=activecell, " option.  This has worked for me.
0
 

Author Comment

by:murbro
ID: 18883737
thanks very much
0

Featured Post

Want to promote your upcoming event?

Are you going to an event? Are you going to be exhibiting at a tradeshow? Talking at a conference? Using a promotional banner in your email signature ensures that your organization’s most important contacts stay in the know and can potentially spread the word about the event.

Question has a verified solution.

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

When you start your Windows 10 PC and got an "Operating system not found" error or just saw  "Auto repair for startup" or a blinking cursor with black screen. A loop for Auto repair will start but fix nothing.  You will be panic as there are no back…
How to record audio from input sources to your PC – connected devices, connected preamp to record vinyl discs, streaming media, that play through your audio card: Vista, Windows 7, Windows 8, Windows 8.1 and Windows 10 – both 32 bit & 64.
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

911 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

17 Experts available now in Live!

Get 1:1 Help Now