Results 1 to 1 of 1

Using FindNext to Quantify Change in 2 Single-Word String Lists

Threaded View

Veter Using FindNext to Quantify... 11-05-2008, 12:07 PM
  1. #1
    Registered User
    Join Date
    10-27-2008
    Location
    Chicago
    Posts
    1

    Question Using FindNext to Quantify Change in 2 Single-Word String Lists

    Hi,
    I'm trying to write a simple piece of code that establishes a cell position change in single word string lists. I have two lists with an identical set of string elements, arranged differently from one another. I use the findnext method to search for the first element of one column in the other column, and then when it is found I offset the value of the difference of the rows (i.e. the cell position change) into a third column. The code that I have so far works well in step by step debugging, but apparent iterations give me very large values. At this later stage, the debugger gives me an error 91 message pointing to the line: Loop While Not a Is Nothing And a.Address <> firstaddress. I have tried to use various for and do loop exits but without sucess. Does anyone have ideas of what could be done? Thank you.
    Sub rankchng()
    Dim rCell As Range
    Dim firstaddress As String
    
    Range("D2:D400").Clear
    For Each rCell In Range("b2:b400")
    If rCell = Empty Then Exit For
    
    'Worksheets(Rankings).Range("a2:a400").Value = firstaddress
    With Range("a2:a400")
    Set a = .Find(rCell, LookIn:=xlValues)
    If Not a Is Nothing Then
    firstaddress = a.Address
    
    Do
    'If a = "" Then Exit Do
    a.Offset(0, 3).Value = rCell.Row - a.Row
    
    Set a = .findnext(a)
    
    Loop While Not a Is Nothing And a.Address <> firstaddress
    End If
    End With
    
    Next
    
    End Sub
    Last edited by Leith Ross; 11-05-2008 at 08:50 PM. Reason: Added Code Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1