application-defined or object-defined error

Question

I have this macro running in a file & I am intermittently getting a Run-time error 1004 application-defined or object-defined error.

When I debug it highlights on the line below in bold red.

This happened with exactly the same error on the same line on a different file last week.

Sub logquote()

'

' logquote Macro

' Macro 15/06/2007 by Sharon amended to add filename instead of quote number to log 10/02/2015

'

Dim ThisWorkBook As String

Dim SheetName As String

Dim MyRanges(16) As String

Dim EmptyRow As Integer

Dim a As Integer 'to cyle through ranges

ThisWorkBook = ActiveWorkbook.Name

SheetName = ActiveSheet.Name

'MyRanges(1) = "qdata1" 'Quote No - col B Feb 2015 changed from "qdata1" to become file name

MyRanges(1) = "qdata2" 'Customer Name - col C

MyRanges(2) = "qdata3" 'Value Ex-works - col D

MyRanges(3) = "qdata4" 'Impact Contact - col E

MyRanges(4) = "qdata5" 'Chase Date - col F

MyRanges(5) = "qdata6" 'Country - col G

MyRanges(6) = "qdata7" 'Template version - col H

MyRanges(7) = "qdata8" 'Currency - col I

MyRanges(8) = "qdata9" 'Time - col J

MyRanges(9) = "qdata10" 'Ref/Title - col K

MyRanges(10) = "qdata11" 'P. Code - col L

MyRanges(11) = "qdata12" 'World Area - col M

MyRanges(12) = "qdata13" 'Post Code - col N

MyRanges(13) = "qdata14" 'Cust Contact - col O

MyRanges(14) = "qdata15" 'Cust Email - col P

MyRanges(15) = "qdata16" 'Cust Tel No - col Q

MyRanges(16) = "qdata17" 'Cust Account No - col R

'Log date in column A

'Comments are in last column

    Workbooks.Open Filename:= _

        "S:\Templates\Quotes\Quote_Log.xls"

    Workbooks("Quote_Log.xls").Activate

With Workbooks("Quote_Log.xls")

    .Sheets("Quotes").Activate

    With ActiveSheet

         'find empty row

        EmptyRow = 0

        Do

            EmptyRow = EmptyRow + 1

        Loop Until IsEmpty(.Cells(EmptyRow, 1))

         .Cells(EmptyRow, 1) = Date

         'Log filename in column B

         .Cells(EmptyRow, 2) = ThisWorkBook

                 'fill in other columns (a + 2 = starting column C) from named ranges

        For a = 1 To UBound(MyRanges)

        .Cells(EmptyRow, a + 2) = _

Workbooks(ThisWorkBook).Sheets(SheetName).Range(MyRanges(a))

        Next a

    End With

     'save and close workbook

    .Save

    .Close

End With

 'activate back to where you started

Workbooks(ThisWorkBook).Activate

End Sub

Answer 1

Another error.

The array should be dimensioned as a one based array because you are NOT using the default zero as the first element. Have you got Option Base 1 set in your project? If so, then everything is OK.

However, if Option Base 1 is not set, then the array should be dimensioned as follows and the elements will be 1 to 16.

Dim MyRanges(1 To 16) As String

Dimensioning the array as you have done without using Option Base 1 will result in the elements being 0 to 15.

OssieMac thank you for both of your replies. I am on holiday this week so I will make your suggested changes & mark your replies accordingly next week. I think that my array issue was caused by me editing code, not fully understanding how it worked, & I appreciate you taking the time to point it out to me.

Answer 2

Another error.

The array should be dimensioned as a one based array because you are NOT using the default zero as the first element. Have you got Option Base 1 set in your project? If so, then everything is OK.

However, if Option Base 1 is not set, then the array should be dimensioned as follows and the elements will be 1 to 16.

Dim MyRanges(1 To 16) As String

Dimensioning the array as you have done without using Option Base 1 will result in the elements being 0 to 15.

Answer 3

ThisWorkbook is a reserved word that is an object referencing the workbook containing the VBA code. You have it dimensioned as a string at the top of the code and this is incorrect.

Delete the following line at the top of the code.

Dim ThisWorkBook As String

Edit the problem line of code to the following.

 .Cells(EmptyRow, a + 2) = _

            ThisWorkBook.Sheets(SheetName).Range(MyRanges(a))