Greffin
06-07-2018, 02:45 AM
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
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