Results 1 to 6 of 6

subscript out of range even though worksheet is there

This is a discussion on subscript out of range even though worksheet is there within the Excel Questions forums, part of the Question Forums category; Hi there, I have a problem with the subscript out of range even though my worksheet is actually. code is: ...

  1. #1
    New Member
    Join Date
    Nov 2011
    Posts
    6

    Default subscript out of range even though worksheet is there

    Hi there,

    I have a problem with the subscript out of range even though my worksheet is actually. code is:

    Code:
    Set wbPath = Workbooks.Open("H:\All Projects\test.xlsm", Password:="test")
            Set SPHClinicAnalyzable = wbPath.Worksheets("teste").Range("W:W")
            Set SPHClinicPeriod = wbPath.Worksheets("test").Range("N:N")
            Dim a23 As Double '% of VGH ED of total clinic volume
            
            'adding up all three clinic volumes
            TotalClinicVolume = Application.WorksheetFunction.CountIfs(SPHClinicAnalyzable, "Yes", SPHClinicPeriod, mPeriod) + Application.WorksheetFunction.CountIfs(LGHClinicAnalyzable, "Yes", LGHClinicPeriod, mPeriod) + Application.WorksheetFunction.CountIfs(VGHClinicAnalyzable, "Yes", VGHClinicPeriod, mPeriod)
            
            'error catching
            If Not IsError(TotalClinicVolume) Then
            a23 = a23 / TotalClinicVolume
            Worksheets("CCM Analysis by Period").Range("A23").Value = a23
            Else
            Worksheets("CCM Analysis by Period").Range("A23").Value = 0
            MsgBox "No patient yet from all three clinics."
            End If
    error is on the one below a23 = a23 / totalclinicvolume.

    Any thoughts?

  2. #2
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638

    Default Re: subscript out of range even though worksheet is there

    What workbook is the sheet "CCM Analysis by Period" in? The code is looking for it in wbPath

  3. #3
    New Member
    Join Date
    Nov 2011
    Posts
    6

    Default Re: subscript out of range even though worksheet is there

    Quote Originally Posted by Firefly2012 View Post
    What workbook is the sheet "CCM Analysis by Period" in? The code is looking for it in wbPath
    CCM analysis by period is the main calling workbook.

    I have basically defined some other variables even before the

    Code:
    Set wbPath = Workbooks.Open("H:\All Projects\test.xlsm", Password:="test")         Set SPHClinicAnalyzable = wbPath.Worksheets("teste").Range("W:W")         Set SPHClinicPeriod = wbPath.Worksheets("test").Range("N:N")         Dim a23 As Double '% of VGH ED of total clinic volume

  4. #4
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638

    Default Re: subscript out of range even though worksheet is there

    Presumably then you need to prefix the Worksheets() with ThisWorkbook as the activeworkbook (wbPath) doesn't have this sheet hence the subscript out of range error

  5. #5
    New Member
    Join Date
    Nov 2011
    Posts
    6

    Default Re: subscript out of range even though worksheet is there

    Quote Originally Posted by Firefly2012 View Post
    Presumably then you need to prefix the Worksheets() with ThisWorkbook as the activeworkbook (wbPath) doesn't have this sheet hence the subscript out of range error
    Are you suggesting that I put that statement outside the new workbook definition?

    when the new workbook opens, which workbook becomes the activated? the newly opened? If so, I would prefer that the original calling workbook remains active.

    How do I do so?

    Thank you

  6. #6
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638

    Default Re: subscript out of range even though worksheet is there

    You would need to re-activate it (the opened workbook becomes the active one) - so after the Workbooks.Open statement you would need a

    ThisWorkbook.Activate

    line.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com