Solved

Excel VBA FindNext Error

Posted on 2007-03-23
2
2,327 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Our Group Policy work started with Small Business Server in 2000. Microsoft gave us an excellent OU and GPO model in subsequent SBS editions that utilized WMI filters, OU linking, and VBS scripts. These are some of experiences plus our spending a lo…
Sometimes drives fill up and we don't know why.  If you don't understand the best way to use the tools available, you may end up being stumped as to why your drive says it's not full when you have no space left!  Here's how you can find out...
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
This Micro Tutorial will give you a basic overview of Windows DVD Burner through its features and interface. This will be demonstrated using Windows 7 operating system.

708 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