PDA

View Full Version : [SOLVED] Subscript out of range



Cinema
03-18-2019, 02:51 AM
Hi,

Im getting an error "Subscript out of range" as the worksheet does not exist. But I have an error handling in my code but this does not apply.


Option Explicit

Public Sub Copy_Input()
Dim wbkInput As Workbook
Dim wbkBackup As Workbook
Dim wrsInput As Worksheet
Dim wrsBackup As Worksheet
Dim Path As String
Dim Dateiname As String
Dim strBerichtsmonat As String
Dim strFormulaNewMonth As String

Dim int_AktuellerMonat As Integer
Dim int_AktuellesJahr As Integer
Dim intLastRow As Integer
Dim intLength As Integer

Dim i, LengthRange As Long

Dim aryPfad As Variant
Dim arrayMonate As Variant
Dim varSearchInFormula As Variant


Dim fbCalcMode As XlCalculation
Dim BackUpRange As Range


arrayMonate = Array("Januar", "Februar", "März", "April", "Mai", "Juni", "Juli", "August", "September", "Oktober", "November", "Dezember")

Set wbkBackup = Application.ActiveWorkbook



strBerichtsmonat = arrayMonate(1)

Set wrsBackup = wbkBackup.Worksheets(strBerichtsmonat) <---- ERROR



If wrsBackup Is Nothing Then
MsgBox "Sheet does not exist"
Exit Sub
End If

MagPower
03-18-2019, 04:37 AM
Since an array index starts at 0 (not 1), arrayMonate(1) equals "Februar", and so wbkBackup.Worksheets(strBerichtsmonat) is looking for a worksheet named "Februar". Is that worksheet in your workbook?

Fluff
03-18-2019, 06:44 AM
You need to do it like
On Error Resume Next
Set wrsBackup = wbkBackup.Worksheets(strBerichtsmonat) < ----Error
On Error GoTo 0

If wrsBackup Is Nothing Then
MsgBox "Sheet does not exist"
Exit Sub
End If
Or use a function to check if the sheet exists like
If ShtExists(strBerichtsmonat, wbkBackup) Then
Set wrsBackup = wbkBackup.Worksheets(strBerichtsmonat)
Else
MsgBox "Sheet does not exist"
Exit Sub
End If


Public Function ShtExists(ShtName As String, Optional Wbk As Workbook) As Boolean
If Wbk Is Nothing Then Set Wbk = ActiveWorkbook
On Error Resume Next
ShtExists = (LCase(Wbk.Sheets(ShtName).Name) = LCase(ShtName))
On Error GoTo 0
End Function

Cinema
03-18-2019, 07:33 AM
Hi Fluff I also used the Error handling method. Thank you so much for your suggestions.

Fluff
03-18-2019, 08:12 AM
You're welcome & thanks for the feedback