Results 1 to 5 of 5

A Range in an Array causing macro to fail

Threaded View

  1. #1
    Registered User
    Join Date
    01-13-2009
    Location
    Prince Rupert, BC
    MS-Off Ver
    Excel 97, 2003
    Posts
    54

    A Range in an Array causing macro to fail

    I'm working on a project where I need to match up names in the same row but in 2 different columns. A have a macro written to work the way I want. I'm using names from Column D in an array and using the find function to match names in Column A. The successful find results is placed in Column B.

    Presently, I'm adding the names from Column D into the array manually. I have tried adding these programmatically, but the macro fails.

    Hoping someone will spot where I'm going wrong.

    Sub MarkCellsInColumnA()
        Dim FirstAddress As String
        Dim lookfor As String
        Dim MyArr As Variant
        Dim Rng As Range
        Dim lrA As Long, LrD As Long
        Dim i As Long
        
        lrA = Cells(Rows.Count, "A").End(xlUp).Row
        LrD = Cells(Rows.Count, "D").End(xlUp).Row
    
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
    
    'The line directly below (commented out) is the one I wish to use
    'but it does not work...
    'MyArr = Range("D1:D" & LrD).Value
    
    'The line directly below provides the results I reguire.
    
        MyArr = Array("wally", "paul", "laura", "catherine", "pete", "ron", "bill", _
                    "geo", "george", "tom")
        With Sheets("Sheet1").Range("A1:A" & lrA)
            Range("B1:B" & lrA).ClearContents
    
            For i = LBound(MyArr) To UBound(MyArr)
                
                Set Rng = .Find(What:=MyArr(i), _
                                After:=.Cells(.Cells.Count), _
                                LookIn:=xlFormulas, _
                                LookAt:=xlPart, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
    
                If Not Rng Is Nothing Then
                    FirstAddress = Rng.Address
                    Do
                        Rng.Offset(0, 1).Value = "x"
                        Set Rng = .FindNext(Rng)
                    Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
                End If
            Next i
        End With
    
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
        End With
    End Sub
    Thanks
    Mister P
    Attached Files Attached Files
    Last edited by Mister P; 12-30-2011 at 06:57 AM.

Thread Information

Users Browsing this Thread

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

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