Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Feb 2001
    Location
    Glasgow, Scotland
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Workbook Variables (Excel 97)

    I have set up some VBA to declare a workbook as a variable, but I am continually getting subscript out of range error. Checking the variables the one set for the workbook "Set Startwksht = ActiveWorkbook"
    does not have anything appear when you hover in debug mode. I have used the following declaration "Dim Startwksht As Workbook"


    Basically what I am trying to do is to go through a series of worksheets within a series of workbooks in a directory and copy a column named results which appear in each worksheet and put them into a corresponding sheet in the calling spreadsheet offset each time through the loop of workbooks.

    cheers

    Angus

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Workbook Variables (Excel 97)

    Can you post the code you are using? (Not just one or two lines)

  3. #3
    Lounger
    Join Date
    Feb 2001
    Location
    Glasgow, Scotland
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook Variables (Excel 97)

    The code
    With commented bits I have tried to get round the subscript problem.

    Option Explicit

    Sub FindXLSFiles()

    Dim lFile As Long
    Dim Startwksht As Workbook
    Dim nextoffset As Integer
    Dim currwkbook As Workbook
    Dim destrange, c As Range
    Dim Currsheet As Worksheet
    Dim i, j As Integer
    Dim xbook As String

    j = 0
    'Set Startwksht = Workbooks("zeros.xls")
    xbook = ActiveWorkbook.Name

    'Set Startwksht = Workbooks(xbook)

    Set Startwksht = ThisWorkbook

    With Application.FileSearch
    .NewSearch
    .FileType = msoFileTypeExcelWorkbooks
    .LookIn = "I:Answers"
    .SearchSubFolders = False
    .Execute SortBy:=msoSortByFileType
    If .FoundFiles.Count Then
    For lFile = 1 To .FoundFiles.Count
    On Error GoTo myerrhand
    Workbooks.Open FileName:=.FoundFiles(lFile), updatelinks:=0
    xbook = ActiveWorkbook.Name
    'Set CurrWkBook = ThisWorkbook
    Application.StatusBar = "Now working on " & ActiveWorkbook.FullName

    'set the offest in the sheets below to the
    nextoffset = j + 1

    'Set CurrWkBook = Workbooks("I:Answerszeros.xls")


    For i = 4 To 16
    Set Currsheet = ActiveWorkbook.Sheets(i)

    'Set destrange = Startwksht.Worksheets(i).Range(0, 16).Offset(nextoffset)

    With Currsheet.UsedRange
    Set c = .Find("Result", LookIn:=xlValues)
    End With
    c.EntireColumn.Copy Startwksht.Worksheets(i).Range(0, 16).Offset(nextoffset)

    Next


    'ActiveWorkbook.Save
    Application.DisplayAlerts = False
    Windows(xbook).Close
    myerrhand:
    Windows(ActiveWorkbook.Name).Activate
    Next lFile
    End If
    End With
    Application.StatusBar = False
    End Sub

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook Variables (Excel 97)

    Your code was a little hard to understand, but I think the code below does what you were trying to do:

    <pre>Public Sub FindXLSFiles()
    Dim I As Long, J As Long, K As Long
    Dim oTgtWB As Workbook, oNxtWB As Workbook, oResult As Range
    Set oTgtWB = ActiveWorkbook
    K = 0
    With Application.FileSearch
    .NewSearch
    .FileType = msoFileTypeExcelWorkbooks
    .LookIn = "I:Answers"
    .SearchSubFolders = False
    .Execute SortBy:=msoSortByFileType
    If .FoundFiles.Count Then
    For I = 1 To .FoundFiles.Count
    Set oNxtWB = Workbooks.Open(Filename:=.FoundFiles(I))
    Application.StatusBar = "Now working on " & oNxtWB.FullName
    For J = 4 To 16
    On Error Resume Next
    Set oResult = Nothing
    Set oResult = oNxtWB.Worksheets(J).Cells.Find(what:="Result", LookIn:=xlValues)
    On Error GoTo 0
    If Not oResult Is Nothing Then
    oResult.EntireColumn.Copy Destination:=oTgtWB.Worksheets(1).Range("P1").Offs et(0, K)
    K = K + 1
    End If
    Next J
    oNxtWB.Close
    Next I
    End If
    End With
    End Sub
    </pre>

    Legare Coleman

  5. #5
    Lounger
    Join Date
    Feb 2001
    Location
    Glasgow, Scotland
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook Variables (Excel 97)

    Thanks Legare

    The only change I had to make was to have the offset counter outside of the next loop. The code was exactly what I required.

    Cheers

    Angus

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook Variables (Excel 97)

    I am not sure I understand. Are you saying that you moved the K = K + 1 outside the For J = 4 To 16 loop? If so, then all that you are getting copied is the column from the last worksheet. If that is what you want, and if all of the worksheets have a "Result" column, then you can eliminate the loop completely with:

    <pre> Set oResult = Nothing
    Set oResult = oNxtWB.Worksheets(Worksheets.Count).Cells.Find(wha t:="Result", LookIn:=xlValues)
    On Error GoTo 0
    If Not oResult Is Nothing Then
    oResult.EntireColumn.Copy Destination:=oTgtWB.Worksheets(1).Range("P1").Offs et(0, K)
    K = K + 1
    End If
    </pre>

    Legare Coleman

  7. #7
    Lounger
    Join Date
    Feb 2001
    Location
    Glasgow, Scotland
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook Variables (Excel 97)

    Unfortunately the first 3 sheets are instructional sheets on how to use the spreadsheet. The fourth to fourteenth sheets have the data I want to summarise into one work book in the same format of worksheets. (I had one more change in that the oTgtWB.Worksheets(1). had to change to be J instead of 1). Therefore I couldn't, in my instance, dispence with the loop.

    Thanks for your help

    Angus

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook Variables (Excel 97)

    OK, then moving the offset counter outside the loop was not the only change you made. Changing the 1 to J makes a big difference. You are putting the columns on different sheets. In that case, you do need the loop.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •