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?