Ran into a "feature" that I am curious about. If anyone has a better explanation please share.
In this process I am scanning every table in a workbook for a specific column heading (PartNumber) so that I can compare every part number found against the master list to make sure it exists there.
I found in testing the code that this line "seems" to work (tblbodyX is a Variant)
but that the following line gets an error
To make it work I had to change the code to look for the number of rows first. If just one row, load the entire table into tblBodyX, not just the column that I wanted. (See below)
What I see is that because I am only loading one row and one column (originally) that Excel failed to build an array (of one cell) within the variant field.
Is that correct or is there something else that I am missing???
Thank you,
Brian
In this process I am scanning every table in a workbook for a specific column heading (PartNumber) so that I can compare every part number found against the master list to make sure it exists there.
I found in testing the code that this line "seems" to work (tblbodyX is a Variant)
tblBodyX = loX.DataBodyRange.Columns(ColX)
but that the following line gets an error
For RowX = LBound(tblBodyX) To UBound(tblBodyX)
To make it work I had to change the code to look for the number of rows first. If just one row, load the entire table into tblBodyX, not just the column that I wanted. (See below)
What I see is that because I am only loading one row and one column (originally) that Excel failed to build an array (of one cell) within the variant field.
Is that correct or is there something else that I am missing???
Thank you,
Brian
Rich (BB code):
For Each wsX In wbX.Worksheets
Me.wsName.Caption = wsX.Name
Me.tblName.Caption = vbNullString
Call ShowDoStatus(Delay1, "Starting part number search in '" & wbX.Name & "' '" & wsX.Name & "' ...")
If Not (wsX.Name Like ctbl_Parts) _
And Not (wsX.Name Like ctbl_Parts_Use) _
And Not (wsX.Name Like cFAQ) _
And Not (wsX.Name Like cTOC) Then
'worksheet found that may contain part number information
For Each loX In wsX.ListObjects
Me.tblName.Caption = loX.Name
Call ShowDoStatus(Delay1, "Starting part number search in '" & wbX.Name & "' '" & wsX.Name & "' '" & loX.Name & "' ...")
tblHeaderX = loX.HeaderRowRange
For ColX = LBound(tblHeaderX, 2) To UBound(tblHeaderX, 2)
'checking each column heading of every table for "PartNumber*" or "Part Number*"
If tblHeaderX(1, ColX) Like cPartNumber & cAsterisk _
Or tblHeaderX(1, ColX) Like cPart_Number & cAsterisk Then
Me.tblName.Caption = loX.Name
If loX.DataBodyRange.Columns(ColX).Count > 1 Then
tblBodyX = loX.DataBodyRange.Columns(ColX) 'moves only part numbers to array
ColX = 1 'column number has changed
Else
tblBodyX = loX.DataBodyRange 'only one record found, move entire table so that there is an array in variant
End If
For RowX = LBound(tblBodyX) To UBound(tblBodyX)
If Trim(tblBodyX(RowX, ColX)) <> vbNullString Then
Call tblPartsUse_Update
If Me.DoStatus.Caption Like cErrorMsg Then GoTo Exit_wbX_Scan
End If
Next RowX
End If
Exit For 'Next ColX
Next ColX
Next loX
End If
Next wsX