Run-time Error 438

[Greffin]

Hi,

I need to set a VBA code so that when I open the workbook it will automatically take me to a predefined cell within the workbook.

I came up with this:

Private Sub Workbook_Open()

Application.Goto Worksheets("Summary").Range("A276"), True

End Sub

This code works perfectly.

Now my issue is that the range needs to change based on the day on which I am opening the workbook, so I build in an IF Else Then variable as so:

Private Sub Workbook_Open()
If Date < Worksheets("BasisData").Cell(s, 3) Then
Application.Goto Worksheets("Summary").Range("A44"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 4) Then
Application.Goto Worksheets("Summary").Range("A52"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 5) Then
Application.Goto Worksheets("Summary").Range("A60"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 6) Then
Application.Goto Worksheets("Summary").Range("A68"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 7) Then
Application.Goto Worksheets("Summary").Range("A76"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 8) Then
Application.Goto Worksheets("Summary").Range("A84"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 9) Then
Application.Goto Worksheets("Summary").Range("A92"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 10) Then
Application.Goto Worksheets("Summary").Range("A100"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 11) Then
Application.Goto Worksheets("Summary").Range("A108"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 12) Then
Application.Goto Worksheets("Summary").Range("A116"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 13) Then
Application.Goto Worksheets("Summary").Range("A124"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 14) Then
Application.Goto Worksheets("Summary").Range("A132"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 15) Then
Application.Goto Worksheets("Summary").Range("A140"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 16) Then
Application.Goto Worksheets("Summary").Range("A148"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 17) Then
Application.Goto Worksheets("Summary").Range("A156"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 18) Then
Application.Goto Worksheets("Summary").Range("A164"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 19) Then
Application.Goto Worksheets("Summary").Range("A172"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 20) Then
Application.Goto Worksheets("Summary").Range("A180"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 21) Then
Application.Goto Worksheets("Summary").Range("A188"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 22) Then
Application.Goto Worksheets("Summary").Range("A196"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 23) Then
Application.Goto Worksheets("Summary").Range("A204"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 24) Then
Application.Goto Worksheets("Summary").Range("A212"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 25) Then
Application.Goto Worksheets("Summary").Range("A220"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 26) Then
Application.Goto Worksheets("Summary").Range("A228"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 27) Then
Application.Goto Worksheets("Summary").Range("A236"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 28) Then
Application.Goto Worksheets("Summary").Range("A244"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 39) Then
Application.Goto Worksheets("Summary").Range("A252"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 30) Then
Application.Goto Worksheets("Summary").Range("A260"), True
ElseIf Date < Worksheets("BasisData").Cell(s, 31) Then
Application.Goto Worksheets("Summary").Range("A268"), True
ElseIf Date >= Worksheets("BasisData").Cell(s, 32) Then
Application.Goto Worksheets("Summary").Range("A276"), True
End If
End Sub

But this code give me a Run-time error '438' - I'm not sure how to solve this issue.

Any help would be welcomed.

Thanks

[Aflatoon]

It's Cells, not Cell:

If Date < Worksheets("BasisData").Cells(s, 3) Then

[Paul_Hossler]

You can greatly simplify that really long If-Then/ElseIf-Then …...

Option Explicit

Private Sub Workbook_Open()
    'never Dim-ed s
    Dim s As Long
    s = 1               '   change as needed
    
    Dim i As Long
    
    With Worksheets("BasisData")
        For i = 3 To 32
            If Date < .Cell(s, i) Then
                Application.Goto .Cells(1, 8 * i + 20), True
                Exit For
            End If
        Next i
    End With
End Sub

[Greffin]

I tried both options and I still have an error showing up...

On the 1st suggested solution I get: a Run-time error '1004'
and on the 2nd suggested solution I get again a Run-time error '438'

Not sure how to go forward...

[Paul_Hossler]

It's very helpful if you say what line is generating the error(s) and what the error description is so people don't have to look it up

It's also helpful if you can attach a small workbook that demonstrates the error(s)

[Greffin]

Sry about that.
on your solution the error is highlighted on line 12...

attached is my workbook

[Paul_Hossler]

1. Typo

2. Changed row and column numbers to fit your workbook

Option Explicit

Private Sub Workbook_Open()
    Dim i As Long
    
    With Worksheets("BasisData")
        For i = 3 To 54
            If Date < .Cells(i, 18).Value Then
                
'                MsgBox Worksheets("Summary").Cells(8 * i + 20, 1).Address
                
                Worksheets("Summary").Select
                Application.Goto Worksheets("Summary").Cells(8 * i + 20, 1), True
                Exit For
            End If
        Next i
    End With
End Sub

[Greffin]

Thanks a lot.

One more question, how can I add a variable so that when it passes the last date on my list, that it opens to the last listed cell?