Type mismatch when testing if table value is blank.

Question

I have the code listed below.

I get an error 13 (type mismatch) when executing the "TA_Table.DataBodyRange(iRow, 1).Value2 = "" " statement.

The cells in the table have text strings. If I clear out the cells, no error is generated and the code works as it seems it should.

I'm unable to figure out why this error is generated. Can someone point me in the right direction?

    Dim RT_Table As ListObject 

    Set RT_Table = ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:H2"), , xlYes) 

    RT_Table.TableStyle = "TableStyleMedium2" 

' 

' Input column headers 

' 

    RT_Table.HeaderRowRange(1) = "Name" 

    RT_Table.HeaderRowRange(2) = "Email" 

    RT_Table.HeaderRowRange(3) = "Mentor-Mentee" 

    RT_Table.HeaderRowRange(4) = "Partner" 

    RT_Table.HeaderRowRange(5) = "Table" 

    RT_Table.HeaderRowRange(6) = "Phone1" 

    RT_Table.HeaderRowRange(7) = "Phone2" 

    RT_Table.HeaderRowRange(8) = "Email2" 

' 

' Copy the mentor names to the reminder sheet 

' 

    Dim TA_Table As ListObject 

    Set TA_Table = Sheets("Table assignments").ListObjects("Table_assignments") 

    For Each iRow In TA_Table.ListColumns("Mentor Name").DataBodyRange.Rows 

        If TA_Table.DataBodyRange(iRow, 1).Value2 = "" Then 

            RT_Table.ListRows.Add 

            RT_Table.DataBodyRange(iRow, 1).Value2 = TA_Table.DataBodyRange(iRow, 1).Value2 

            End If 

        Next iRow

Answer 1

Jeovany - thanks. That works, of course. I discovered that iRow.row has the row number.

I used that information to revise the code as follows:

    Dim TA_Table As ListObject 

    Set TA_Table = Sheets("Table assignments").ListObjects("Table_assignments") 

    For Each iRow In TA_Table.ListColumns("Mentor Name").DataBodyRange.Rows 

        If iRow.Value2 <> "" Then 

            RT_Table.ListRows.Add 

            RT_Table.DataBodyRange(iRow.Row, 1).Value2 = TA_Table.DataBodyRange(iRow.Row, 1).Value2 

            End If 

        Next iRow 

Which gives no errors.  But it skips the first row, since iRow.Row starts with 2. 
So I presume it is 2 for the first row in DataBodyRange.  If I subtract 1, it works fine. 

Thanks again.

Answer 2

Hi Bert

Re, "... I'm unable to figure out why this error is generated. Can someone point me in the right direction?"

In the For Loop, you are using the variable iRow it is not the row number you are expecting to retrieve

In this case, iRow is a range that will take the value of each cell in the "Mentor Name" column range, which will be a String value hence the error in the If statement line because we expect there that iRow parse a row number, not a string.

Let's say that Peter is the first name in the "Mentor Name" column

Once we start the loop iRow will be equal to "Peter" therefore the line gives an ERROR

If TA_Table.DataBodyRange("Peter", 1).Value2 = "" Then

Solution

Replace the line

If TA_Table.DataBodyRange(iRow, 1).Value2 = "" Then

with

If iRow.Value2="" Then

Regards

Jeovany