string variable showing assigned value from listobject.name as empty

[osmejia]

I'm trying to assign the name of a ListObject (a table) to a string variable, however the variable is showing empty value even though the listobject name does exist and is correctly identified:

'------------------------------------------------------------------------------
'0. Declaration and Initialization of Variables
'------------------------------------------------------------------------------
Dim wb As Workbook
Set wb = ThisWorkbook
Dim wsSheet As Worksheet

Dim dateMasterIP, dateCurrentIP As Date
dateMasterIP = Range("rngMasterIPDate").Value

Dim wsName, strDataMasterIP, strDataCurrentIP, strNewSheetName As String
strDataMasterIP = "tbIP" & Year(dateMasterIP)

Dim objTable As ListObject
Dim TestForTable As String
Dim rngTestCell As Range

Dim rngIPDates() As Variant
rngIPDates = Range("rngIPDates").Value

'------------------------------------------------------------------------------
'2. Procedure Module
'------------------------------------------------------------------------------
For Each wsSheet In Worksheets
wsName = wsSheet.Name

If Left(wsName, 2) = "IP" Then
Set wsSheet = wb.Worksheets(wsName)
Set objTable = wsSheet.ListObjects(1)

Dim dateInspection As Variant
For Each dateInspection In rngIPDates
If Year(dateInspection) = Right(wsName, 4) Then
strDataCurrentIP = objTable.Name
dateCurrentIP = CDate(dateInspection)
End If
Call DataAlignment(wb, strDataMasterIP, strDataCurrentIP, dateMasterIP, dateInspection)
Next dateInspection
End If
Next wsSheet

Srolling over the code, the objTable.Name shows the value "tbIP2018" however the strDataCurrentIP shows an empty value.

 

[Phuoc]

Try change:

If Year(dateInspection) = Right(wsName, 4) Then

to:

If Year(dateInspection) = CLng(Right(wsName, 4)) Then

 

[osmejia]

Try change:

If Year(dateInspection) = Right(wsName, 4) Then

to:

If Year(dateInspection) = CLng(Right(wsName, 4)) Then

Many thanks Phuoc, it worked!

 

[Georgiboy]

Side note:

When you define variables as below:

Dim wsName, strDataMasterIP, strDataCurrentIP, strNewSheetName As String

Only strNewSheetName has been specifically defined. The rest are defined but with no type.

You should define each item as a type as below:

Dim wsName As String, strDataMasterIP As String, strDataCurrentIP As String, strNewSheetName As String