subscript out of range even though worksheet is there

seekyourway

New Member
Joined
Nov 1, 2011
Messages
21
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?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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

</pre>
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,296
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top