CODEWITHSUNDEEP
excelvbams-access
Bread Doughlas
Bread Doughlas

Reputation: 67

Range.End 1004 error when controlling Excel through Access

I am trying to get Access to make an entry in two cells of an Excel workbook each time a form is completed.

I'm getting a 1004 error when using Range().End().

R and C are variables I am using to isolate functions to find out what's causing the problem.

Option Compare Database

Private Sub Form_AfterUpdate()
'This macro will update the Excel WO Tracker for a project each time an NCR is closed or resolved

Dim xl As Object 'Excel
Dim tracker As Object 'Book
Dim sn As Object 'Sheet\
Dim connect(1) As Variant 'WO and NCR#

'Debugging variables
Dim R As Object
Dim C As Object

'Exit if required fields are blank
If (Title.Value = "") Or (status.Value = "") Or (Program.Value = "") Or _
  (Disposition.Value = "") Or ([Work Order Number].Value = "") Then
    Exit Sub
End If
'On Error GoTo Handler

If (status.Value = "Closed") Or (status.Value = "Resolved") Then
    Set xl = CreateObject("Excel.Application")
    xl.ScreenUpdating = False
    Select Case Program.Value
    Case "SE07"
        Set tracker = xl.Workbooks.Open("Z:\Operations\Projects\SE07\" & Year(Now()) & " SE07 WO Tracker.xlsm")
    Case "VS02"
        Set tracker = xl.Workbooks.Open("Z:\Operations\Projects\VS01\" & Year(Now()) & " VS02 WO Tracker.xlsm")
    Case Else 'Program does not have a WO Tracker
        xl.ScreenUpdating = True
        Set xl = Nothing
        Exit Sub
    End Select

    Set sn = tracker.Sheets("SN")
        'Record NCR connection
        connect(0) = [Work Order Number].Value
        connect(1) = Title.Value
        Set R = xl.Intersect(sn.Range("FirstCol"), sn.Range("NCRConnect"))

        'Error 1004 thrown here:
        Set C = R.End(xlToRight)

        xl.Intersect(sn.Range("NCRConnect"), sn.Columns(C.Column + 1)) = xl.Transpose(connect)
    End If

    tracker.Save
    tracker.Close
    Set tracker = Nothing
    Set sn = Nothing
    xl.ScreenUpdating = True
    Set xl = Nothing
End Sub

Also, this code runs extremely slow. Is there a way to speed this up?

Upvotes: 0

Views: 105

Answers (1)

BigBen
BigBen

Reputation: 49998

xlToRight is a member of the Excel Object Model, namely the XlDirection enum.

If you are late-binding, add the following:

Const xlToRight As Long = -4161

Upvotes: 2

Related Questions