+ Reply to Thread
Results 1 to 5 of 5

Range(ActiveCell.offset always fails second time run

Hybrid View

RxMiller Range(ActiveCell.offset... 09-08-2010, 05:43 PM
romperstomper Re: Range(ActiveCell.offset... 09-08-2010, 05:47 PM
RxMiller Re: Range(ActiveCell.offset... 09-08-2010, 06:24 PM
RxMiller Re: Range(ActiveCell.offset... 09-08-2010, 06:28 PM
Paul Re: Range(ActiveCell.offset... 09-08-2010, 06:29 PM
  1. #1
    Registered User
    Join Date
    03-05-2009
    Location
    Vail, Colorado
    MS-Off Ver
    Excel 2010
    Posts
    83

    Unhappy Range(ActiveCell.offset always fails second time run

    Row 505 - hard code in offset value and the code works just fine over and over.
    Row 510 or 511 using variable - It works the first time through, but always fails the second time through Error 91 Object Variable

    500       ObjXL.Workbooks(1).Worksheets(1).Rows((intRowPos - 1) & ":" & (intRowPos - 1)).Select         ' Selection for Bold header column (can make 2 Rows if needed)
            'ObjXL.Range("A" & intRowPos - 1).Select        ' Select the first column at our starting Row for the header
    505        ObjXL.Workbooks(1).Worksheets(1).Range("A" & intRowPos - 1 & ":H" & intRowPos - 1).Select  ' Put Title at one row less than where data starts
    
                ' ****************************** this next line will error the Second Time Through even if all forms are closed back to switchboard ******
    510     'ObjXL.Workbooks(1).Worksheets(1).Range(ActiveCell,Offset(0, 0), ActiveCell.Offset(0, intMaxheaderColCount)).Select
    511        'ObjXL.Range(ActiveCell, ActiveCell.Offset(0, intMaxheaderColCount)).Select
            '   in debug window   ? objxl.Activecell does return correct value in the active cell
    520     Call Send2ExcelRowHeaderFormat(ObjXL)               ' Format the data row heading  bold, outline, as a Select
    Last edited by RxMiller; 09-08-2010 at 06:47 PM. Reason: [Solved]

  2. #2
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    21,980

    Re: Range(ActiveCell.offset always fails second time run

    Instead of ActiveCell use objXL.ActiveCell
    Everyone who confuses correlation and causation ends up dead.

  3. #3
    Registered User
    Join Date
    03-05-2009
    Location
    Vail, Colorado
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Range(ActiveCell.offset always fails second time run

    Wow, I tried and it works Access 2007 calling Excel 2007.
    But of course... how could I have missed that?
    Thanks so much!

    510 ObjXL.Workbooks(1).Worksheets(1).Range(ObjXL.ActiveCell.Offset(0, 0), ObjXL.ActiveCell.Offset(0, intMaxheaderColCount)).Select
    Last edited by RxMiller; 09-08-2010 at 06:25 PM. Reason: spelling

  4. #4
    Registered User
    Join Date
    03-05-2009
    Location
    Vail, Colorado
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Range(ActiveCell.offset always fails second time run

    Directions - how to mark this as Solved ?

  5. #5
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,887

    Re: Range(ActiveCell.offset always fails second time run

    Click the Edit button in the first post in this thread, then click Go Advanced. Next to the thread title there is a Prefix drop-down box. Select 'Solved' and then submit.

+ Reply to Thread

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