Results 1 to 7 of 7

Code fails with runtime error 91 in some case.

Threaded View

  1. #1
    Registered User
    Join Date
    01-09-2013
    Location
    Helsingborg, Sweden
    MS-Off Ver
    Excel 2010
    Posts
    47

    Code fails with runtime error 91 in some case.

    Hello.

    I have pusseld together a code that matches data between ranges on different workbooks and copy pastes information belonging to sertain ranges from wb2 to wb1.

    The code works just fine on sheet "Car" and "Bike" in MainWorkbook. but when run on a more complex sheet "Plane" i get runtime error 91 "objecvariable or with block variable has not been defined". still when i press debug and stop the code it highlights the copy functions for range nr 3. and when i stop code after error and close it, the macro has still done its job as intended.

    Code i use is

    Option Explicit
    
    Sub Import()
    Dim fname As String
    
    Dim Crng As Range, Frng As Range, Irng As Range
    
    Set Crng = ActiveSheet.Range("C7")
    Set Crng = Range(Crng, Crng.End(xlDown))
    
    Set Frng = ActiveSheet.Range("F7")
    Set Frng = Range(Frng, Frng.End(xlDown))
    
    Set Irng = ActiveSheet.Range("I7")
    Set Irng = Range(Irng, Irng.End(xlDown))
    
    fname = ActiveSheet.Name
    
    Workbooks.Open FileName:=Range("M2")
    Sheets(fname).Select
    
    Dim Erng As Range, aCell As Range, bCell As Range
    
    Set Erng = ActiveSheet.Range("E5")
    Set Erng = Range(Erng, Erng.End(xlDown))
    
    For Each aCell In Crng
        Set bCell = Erng.Find(What:=aCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not aCell Is Nothing Then
            aCell.Offset(, -1) = bCell.Offset(, -1)
            aCell.Offset(, 1) = bCell.Offset(, -2)
        End If
        Next
    
    For Each aCell In Frng
        Set bCell = Erng.Find(What:=aCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not aCell Is Nothing Then
            aCell.Offset(, -1) = bCell.Offset(, -1)
            aCell.Offset(, 1) = bCell.Offset(, -2)
        End If
        Next
    
    For Each aCell In Irng
        Set bCell = Erng.Find(What:=aCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not aCell Is Nothing Then
            aCell.Offset(, -1) = bCell.Offset(, -1)
            aCell.Offset(, 1) = bCell.Offset(, -2)
        End If
        Next
        
    ActiveWindow.Close
    
    End Sub
    Could anyone please help me find whats wrong and need be fixing?
    All the help i can get is appriciated!

    Cross Posts at http://www.ozgrid.com/forum/showthread.php?t=181410
    & http://www.mrexcel.com/forum/excel-q...some-case.html

    Cheers
    Attached Files Attached Files
    Last edited by lavinius; 08-02-2013 at 05:17 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Getting a runtime error with this code
    By rinser in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-20-2013, 10:22 AM
  2. [SOLVED] Range error in code, runs alone but not inside my full program, giving runtime error 1004
    By charizzardd in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-23-2012, 03:34 PM
  3. Code fails with error 1004
    By cdjones in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-22-2010, 06:27 PM
  4. Runtime Error Code 1004
    By laks in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-19-2007, 07:08 PM
  5. runtime error 9 problem with this code
    By cuewoz in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-03-2006, 09:54 AM

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