Method 'Range' of object'_Global' failed

[joslaz]

Hey!


I am using the following code, to jump to the first day of the month.
The buttons includes the name of the month ("Jan"...).


It works perfectly at my computer, but not on others.
There is always an error: Method 'Range' of object'_Global' failed on the "Set vRange = Range("TblOP[[#Headers],[" & vDate & "]]")"




Do you know why?

    Sub PrcCurrentMonth()
    
    Dim vNumber As Long
    Dim vDate As Date
    Dim vMonth As Variant
    Dim vRange As Range
    
    
    vMonth = ActiveSheet.Shapes(Application.Caller).DrawingObject.Caption
    vNumber = WorksheetFunction.Match(vMonth, Application.GetCustomListContents(7), 0)
    vDate = DateSerial(2019, vNumber, 1)
    
    Set vRange = Range("TblOP[[#Headers],[" & vDate & "]]")
    
        If Not vRange Is Nothing Then
            Application.Goto vRange, True
            Set vRange = Nothing
    
        End If
    
    End Sub

 

[Tim_Excel_]

I tried googling what TblOP would mean, here on my office desktop. Guess I won't be doing that anymore...
NSFW

 

[Norie]

What headers do you have in the table TblOP?

 

[Yongle]

Either the Table name or header is not being found by VBA

Is the Table name being found?
("Subscript out of Range Error" returned if table not found - check table name)
add these 2 lines as first 2 lines of code

    MsgBox 1 & vbCr & ActiveSheet.ListObjects("TblOP").Name
    exit sub

If Table is found, then the header is not being found
Is the header being found?
add these lines immediately below vDate = DateSerial(2019, vNumber, 1)

   On Error Resume Next
   Dim r As String: r = ActiveSheet.ListObjects("TblOP").HeaderRowRange.Find(vDate, lookat:=xlWhole).Address
   If r = "" Then MsgBox "Not Found" Else MsgBox "found at " & r

 

[Norie]

If you want to find a header in the table why not use ListColumns.Range?

 Set vRange = ActiveSheet.ListObjects("TblOP").ListColumns(vDate).Range.Cells(1,1)