Results 1 to 3 of 3

activecell.offset

Threaded View

steelsoul activecell.offset 07-17-2010, 08:30 AM
Andy Pope Re: activecell.offset problem 07-17-2010, 09:20 AM
steelsoul Re: activecell.offset problem 07-17-2010, 09:23 AM
  1. #1
    Registered User
    Join Date
    07-17-2010
    Location
    I
    MS-Off Ver
    Excel 2007
    Posts
    2

    activecell.offset

    Hello
    In the following code i get a runtime error "Application-defined or object-defined error"
    I know that a 1004 error is hard to find. Let me show you what i realize so far.
    Here is the code
    Sub Updater()
    '
    ' Updater Macro
    '
        Dim MySheetName As String, MyWBName As String, MyPathName As String
        Dim Workers As Integer, jobs As Integer, i As Integer
        Dim wb As Workbook, cwb As Workbook
        Dim temp As Range
        
        Set cwb = ThisWorkbook
        Application.ScreenUpdating = False
        MySheetName = Range("J1").Value & " " & Range("G1").Value 'First value is the cell for the YEAR. Second value is cell for the MONTH
        MyPathName = "f:\BackO\" 'To be changed as PATHS and DRIVES change
        Workers = Range("b1")
        jobs = 9 'UPDATE when number of jobs change
        
        For i = 0 To Workers - 1
            cwb.Worksheets("station").Cells(i * 11 + 3, 1).Select 
            MyWBName = MyPathName & ActiveCell.Value & ".xls"
            If Len(Dir(MyWBName)) = 0 Then
                MsgBox ("this file does not exist" & MyWBName)
            Else
                Set wb = Workbooks.Open(MyWBName)
      ***     cwb.Worksheets("stations").Range(ActiveCell.Offset(1, 1), ActiveCell.Offset(jobs, 31)).Value = wb.Worksheets("july 2010").Range("b4:af12").Value
                wb.Close
            End If
        Next i
        Application.ScreenUpdating = True
        
      
    '
    End Sub
    The error occurs at the line I marked with ***
    After much pain I found that if I change the "ActiveCell.Offset(1, 1), ActiveCell.Offset(jobs, 31)" part to any other range like " "b4:af13" " (like it should amount to at the first loop instance) it runs perfectly.

    Can you please tell me how to fix it so I can run it with the activecell? (you might have noticed I am changing the active cell at each iteration of the loop)

    Thank You
    Last edited by steelsoul; 07-17-2010 at 09:24 AM. Reason: Solved

Thread Information

Users Browsing this Thread

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

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