Results 1 to 10 of 10

Help with Reslving a VBA Code Execution Error

Threaded View

  1. 09-22-2014, 02:42 PM #1
    yegor2000
    yegor2000 is offline
    Registered User
    Join Date
    08-06-2012
    Location
    Houston, TX
    MS-Off Ver
    Excel 2010
    Posts
    7

    Question Help with Reslving a VBA Code Execution Error

    Hello,

    I've written a VBA macro that allows me to perform an automatic data retrieval on daily basis from a PI Server using Excel's add-on "PI DataLink". Within my spreadsheet, certain columns get populated with arrays from PI DataLink while others are formulas that simply need to be copied down daily as new PI data gets retrieved into the spreadsheet. However, when I execute the code, I receive the following error: "Application-defined or object-defined error" after the code activates my "Prod Allocation" worksheet and attempts to select a certain row. The error occurs at the following command: Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Prod Allocation").Range(Cells(i - 2, 8), Cells(i - 2, 122)).Select

    I've checked the code and verified the worksheet name dozens of times to ensure there is not a spelling error, and I'm unable to figure out why the code fails on selecting a certain line. Could you please help me resolve this issue? The code is shown in full below. Not sure if this is relevant, but all coding was done in Excel 2010.

    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Sub Button5_Click()

Dim x As Date
Dim i As Integer
On Error GoTo Errorcatch

For i = 1050 To 5000

If Cells(i, 1).Value = VBA.Date Then
Cells(2, 1).Value = VBA.Date

Worksheets("Reservoir Voidage Replacement").Activate
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Reservoir Voidage Replacement").Range(Cells(i - 2, 10), Cells(i - 2, 13)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 10), Cells(i - 1, 13)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Reservoir Voidage Replacement").Range(Cells(i - 2, 17), Cells(i - 2, 18)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 17), Cells(i - 1, 18)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Reservoir Voidage Replacement").Range(Cells(i - 2, 20), Cells(i - 2, 33)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 20), Cells(i - 1, 33)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Reservoir Voidage Replacement").Range(Cells(i - 2, 46), Cells(i - 2, 48)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 46), Cells(i - 1, 48)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Reservoir Voidage Replacement").Range(Cells(i - 2, 50), Cells(i - 2, 58)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 50), Cells(i - 1, 58)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Reservoir Voidage Replacement").Range(Cells(i - 2, 60), Cells(i - 2, 88)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 60), Cells(i - 1, 88)), Type:=xlFillCopy

Worksheets("Prod Allocation").Activate
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Prod Allocation").Range(Cells(i - 2, 8), Cells(i - 2, 122)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 8), Cells(i - 1, 122)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Prod Allocation").Range(Cells(i - 2, 125), Cells(i - 2, 125)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 125), Cells(i - 1, 125)), Type:=xlFillCopy

Worksheets("Inj Allocation").Activate
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Inj Allocation").Range(Cells(i - 2, 3), Cells(i - 2, 5)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 3), Cells(i - 1, 5)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Inj Allocation").Range(Cells(i - 2, 7), Cells(i - 2, 9)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 7), Cells(i - 1, 9)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Inj Allocation").Range(Cells(i - 2, 11), Cells(i - 2, 15)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 11), Cells(i - 1, 15)), Type:=xlFillCopy

Worksheets("Inj Index (Source)").Activate
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Inj Index (Source)").Range(Cells(i - 2, 3), Cells(i - 2, 5)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 3), Cells(i - 1, 5)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Inj Index (Source)").Range(Cells(i - 2, 8), Cells(i - 2, 8)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 8), Cells(i - 1, 8)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Inj Index (Source)").Range(Cells(i - 2, 13), Cells(i - 2, 21)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 13), Cells(i - 1, 21)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Inj Index (Source)").Range(Cells(i - 2, 23), Cells(i - 2, 25)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 23), Cells(i - 1, 25)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Inj Index (Source)").Range(Cells(i - 2, 28), Cells(i - 2, 28)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 28), Cells(i - 1, 28)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Inj Index (Source)").Range(Cells(i - 2, 33), Cells(i - 2, 41)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 33), Cells(i - 1, 41)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Inj Index (Source)").Range(Cells(i - 2, 43), Cells(i - 2, 45)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 43), Cells(i - 1, 45)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Inj Index (Source)").Range(Cells(i - 2, 48), Cells(i - 2, 48)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 48), Cells(i - 1, 48)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Inj Index (Source)").Range(Cells(i - 2, 53), Cells(i - 2, 63)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 53), Cells(i - 1, 63)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Inj Index (Source)").Range(Cells(i - 2, 66), Cells(i - 2, 67)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 66), Cells(i - 1, 67)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Inj Index (Source)").Range(Cells(i - 2, 66), Cells(i - 2, 67)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 66), Cells(i - 1, 67)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Inj Index (Source)").Range(Cells(i - 2, 75), Cells(i - 2, 79)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 75), Cells(i - 1, 79)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Inj Index (Source)").Range(Cells(i - 2, 91), Cells(i - 2, 92)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 91), Cells(i - 1, 92)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Inj Index (Source)").Range(Cells(i - 2, 100), Cells(i - 2, 114)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 100), Cells(i - 1, 114)), Type:=xlFillCopy

Worksheets("DD & PI (Source)").Activate
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("DD & PI (Source)").Range(Cells(i - 2, 5), Cells(i - 2, 5)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 5), Cells(i - 1, 5)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("DD & PI (Source)").Range(Cells(i - 2, 9), Cells(i - 2, 20)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 9), Cells(i - 1, 20)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("DD & PI (Source)").Range(Cells(i - 2, 24), Cells(i - 2, 24)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 24), Cells(i - 1, 24)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("DD & PI (Source)").Range(Cells(i - 2, 29), Cells(i - 2, 39)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 29), Cells(i - 1, 39)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("DD & PI (Source)").Range(Cells(i - 2, 43), Cells(i - 2, 43)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 43), Cells(i - 1, 43)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("DD & PI (Source)").Range(Cells(i - 2, 47), Cells(i - 2, 58)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 47), Cells(i - 1, 58)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("DD & PI (Source)").Range(Cells(i - 2, 62), Cells(i - 2, 62)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 62), Cells(i - 1, 62)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("DD & PI (Source)").Range(Cells(i - 2, 66), Cells(i - 2, 77)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 66), Cells(i - 1, 77)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("DD & PI (Source)").Range(Cells(i - 2, 85), Cells(i - 2, 100)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 85), Cells(i - 1, 100)), Type:=xlFillCopy

Worksheets("Adjusted Allocation").Activate
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Adjusted Allocation").Range(Cells(i - 2, 8), Cells(i - 2, 116)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 8), Cells(i - 1, 116)), Type:=xlFillCopy

Worksheets("Adjusted Oil Volumes").Activate
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Adjusted Oil Volumes").Range(Cells(i - 2, 2), Cells(i - 2, 7)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 2), Cells(i - 1, 7)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Adjusted Oil Volumes").Range(Cells(i - 2, 9), Cells(i - 2, 12)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 9), Cells(i - 1, 12)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Adjusted Oil Volumes").Range(Cells(i - 2, 24), Cells(i - 2, 26)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 24), Cells(i - 1, 26)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Adjusted Oil Volumes").Range(Cells(i - 2, 33), Cells(i - 2, 34)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 33), Cells(i - 1, 34)), Type:=xlFillCopy
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Adjusted Oil Volumes").Range(Cells(i - 2, 85), Cells(i - 2, 86)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 85), Cells(i - 1, 86)), Type:=xlFillCopy

Worksheets("Field Prod vs Budget Calcs").Activate
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("Field Prod vs Budget Calcs").Range(Cells(i - 2, 2), Cells(i - 2, 40)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 2), Cells(i - 1, 40)), Type:=xlFillCopy

Worksheets("GOR Model Template").Activate
Workbooks("Voidage Replacement & Production Allocation.xlsm").Sheets("GOR Model Template").Range(Cells(i - 2, 2), Cells(i - 2, 25)).Select
Selection.AutoFill Destination:=Range(Cells(i - 2, 2), Cells(i - 1, 25)), Type:=xlFillCopy

Worksheets("Reservoir Voidage Replacement").Activate

MsgBox "Data Retrieval is Completed"

Else: End If

Next

Errorcatch: MsgBox Err.Description

End Sub
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Last edited by yegor2000; 09-22-2014 at 03:00 PM.
    Reply With QuoteRegister To Reply
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA Code Error Upon Execution
    By MacroMan87 in forum PowerPoint Programming
    Replies: 4
    Last Post: 09-21-2013, 01:28 AM
  2. Automation Error At Odd Spot in Code Execution
    By Jenn68 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-02-2012, 11:50 AM
  3. Code execution has been interrupted error
    By vpnvipin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-05-2012, 04:35 AM
  4. Error: Code Execution Has Been Interrupted
    By Xrull in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-07-2010, 07:32 AM
  5. error: Code Execution has been interrupted
    By Joshua Fredrickson in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-23-2005, 11:50 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1