+ Reply to Thread
Results 1 to 7 of 7

VBA Loop, Find & Copy run-time error '91': Object variable or With block variable not set

  1. #1
    Registered User
    Join Date
    10-08-2010
    Location
    North Yorkshire, England
    MS-Off Ver
    Excel 2003
    Posts
    69

    VBA Loop, Find & Copy run-time error '91': Object variable or With block variable not set

    Hello

    I'm getting an error message run-time error '91': Object variable or With block variable not set

    PHP Code: 
    Dim sStart As String
        Dim lTargetRow 
    As Long
        Dim wsSource 
    As Worksheet
        Dim wsTarget 
    As Worksheet
        Dim rSourceCell 
    As Range
        Dim LastRow 
    As Long
        Dim searchRange 
    As Range
         
        
         
    'Setup
        Application.ScreenUpdating = False
        
        
        Set wsTarget = Sheets("Sub-Analysis OUTPUT Desc")
        Set wsSource = Sheets("Sub-Analysis Core")
        
        Do While Not IsEmpty(rSourceCell)
        
       With wsSource
            LastRow = Range("B65536").End(xlUp).Row
       End With
        
        Set searchRange = Sheets("Sub-Analysis Core").Range("B5:B" & LastRow)
                 '
    Get last used row in Target Sheet
            With wsTarget
                lTargetRow 
    = .Range("B65536").End(xlUp).Offset(10).Row
            End With
        
             
    'Get item being searched
             
            searchRange.Find(What:=Subname, After:=Range("B5"), LookIn:=xlValues, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNextRow, MatchCase:= _
            False).Activate
                
             '
    Save start address for comparison
            sStart 
    ActiveCell.Address
            
    Do
        
    'copy find result to target sheet
                ActiveCell.Copy Destination:=wsTarget.Cells(lTargetRow, 3)
                ActiveCell.Offset(0, -1).Copy Destination:=wsTarget.Cells(lTargetRow, 2)
                ActiveCell.Offset(0, 2).Copy Destination:=wsTarget.Cells(lTargetRow, 5)
                ActiveCell.Offset(0, 3).Copy Destination:=wsTarget.Cells(lTargetRow, 10)
       
              '
    Set destination plus one Row
                lTargetRow 
    lTargetRow 1

                 
    'Find next item (til done)
                Cells.FindNext(After:=ActiveCell).Activate
            Loop Until ActiveCell.Address = sStart
            Set rSourceCell = rSourceCell.Offset(1, 0)    <<<<<< ERROR HERE  
    Loop 
    Basically I'm searching for specific text (Subname), picked up via user input thorugh InputBox, in a single column (column B in wsSource), copying that to wsTarget then going back to pick up other data in other columns on the same row, copying that data to wsTarget as well. Then loop to find next instance of Subname and repeat the copying priocess.
    I'm experiencing a problem whereby the Find function is searching the whole sheet and not just column B.

    Help on the problem(s) as ever much appreciated.

    Thanks

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: VBA Loop, Find & Copy run-time error '91': Object variable or With block variable not

    Comment this line
    Find next item (til done)
    If solved remember to mark Thread as solved

  3. #3
    Registered User
    Join Date
    10-08-2010
    Location
    North Yorkshire, England
    MS-Off Ver
    Excel 2003
    Posts
    69

    Re: VBA Loop, Find & Copy run-time error '91': Object variable or With block variable not

    Comment mark was lost in pasting to this thread. Already commented in original.

  4. #4
    Registered User
    Join Date
    10-08-2010
    Location
    North Yorkshire, England
    MS-Off Ver
    Excel 2003
    Posts
    69

    Re: VBA Loop, Find & Copy run-time error '91': Object variable or With block variable not

    ok sorted the error message, I hadn't set the rSourceCell. Should have inserted
    PHP Code: 
    Set rSourceCell Range("B5"
    after With wsource. Nevertheless I'm still experiencing the issue whereby the Find function searches the whole page but I only want to search Column B. Any ideas?
    Thanks

  5. #5
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: VBA Loop, Find & Copy run-time error '91': Object variable or With block variable not

    attach a sample file for testing

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: VBA Loop, Find & Copy run-time error '91': Object variable or With block variable not

    Which line of code do you get the error on?
    If posting code please use code tags, see here.

  7. #7
    Registered User
    Join Date
    10-08-2010
    Location
    North Yorkshire, England
    MS-Off Ver
    Excel 2003
    Posts
    69

    SOLVED: VBA Loop, Find & Copy run-time error '91': Object variable or With block

    ok I found the problem with restricting to one column. Have replaced the line (3rd from end):

    PHP Code: 
    Cells.FindNext(After:=ActiveCell).Activate 
    with

    HTML Code: 
    thanks for your input

+ Reply to Thread

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