DataBodyRange.Rows.Count

[lwebbie]

I have a macro that I want it to look at a table and if there is data in the table then I want it to complete a VLookup but if there is no data in the table then I want it to end the if statement and move to the next statement (there are 3 tables in total the macro moves through). It is always possible for one or more of the tables to not have data.
Currently I have the below written for the first table but when there is no data it just throws an error message.

Workbooks("Letter_Mail Merge_Template.xlsm").Worksheets("MailMerge_Device").Activate
If ActiveSheet.ListObjects("Table24").DataBodyRange.Rows.Count >= 1 Then
Workbooks("Letter_Mail Merge_Template.xlsm").Worksheets("MailMerge_Device").Range("L2:L" & Cells(Rows.Count, "H").End(xlUp).Row).Formula = "=VLOOKUP([@[Full Name]],campusship.csv!$E:$F,2,FALSE)"
Else
End If

If there is data in the table everything works perfectly.

 

[Yongle]

There are 2 risks of error causing the code to fail
- empty table
- the lookup finding nothing (even if you never expect it to happen )

Ensure that code can handle either error
- for clarity, I prefer to use 2 separate IF statements like this

    Dim msg As String
other code...
   
    Workbooks("Letter_Mail Merge_Template.xlsm").Worksheets("MailMerge_Device").Activate
   
'is the table empty?
    On Error Resume Next
        Debug.Print ActiveSheet.ListObjects("Table24").DataBodyRange.Rows.Count
        If Err.Number > 0 Then
            msg = "table is empty"
            GoTo ErrHandling
        End If
   
'does the lookup find anything?
        Workbooks("Letter_Mail Merge_Template.xlsm").Worksheets("MailMerge_Device").Range("L2:L" & Cells(Rows.Count, "H").End(xlUp).Row).Formula = "=VLOOKUP([@[Full Name]],campusship.csv!$E:$F,2,FALSE)"
        If Err.Number > 0 Then
            msg = "lookup found nothing"
            GoTo ErrHandling
        End If
    On Error GoTo 0
   
 other code...
   
Exit Sub                'required to hop over error handling if all is OK

ErrHandling:
    MsgBox msg
End Sub

notes
- the code tests for empty table with Debug.Print rather than using IF test which simplifies the coding

 

[lwebbie]

I've never done Debug.Print it worked like a charm!

Thanks.

 

[mrahulkanth]

There are 2 risks of error causing the code to fail
- empty table
- the lookup finding nothing (even if you never expect it to happen )

Ensure that code can handle either error
- for clarity, I prefer to use 2 separate IF statements like this

    Dim msg As String
[COLOR=#ff0000][I]other code...
[/I][/COLOR]   
    Workbooks("Letter_Mail Merge_Template.xlsm").Worksheets("MailMerge_Device").Activate
   
[COLOR=#008080]'is the table empty?[/COLOR]
    [B]On Error Resume Next[/B]
        Debug.Print ActiveSheet.ListObjects("Table24").DataBodyRange.Rows.Count
        If Err.Number > 0 Then
            msg = "table is empty"
            GoTo ErrHandling
        End If
   
[COLOR=#008080]'does the lookup find anything?[/COLOR]
        Workbooks("Letter_Mail Merge_Template.xlsm").Worksheets("MailMerge_Device").Range("L2:L" & Cells(Rows.Count, "H").End(xlUp).Row).Formula = "=VLOOKUP([@[Full Name]],campusship.csv!$E:$F,2,FALSE)"
        If Err.Number > 0 Then
            msg = "lookup found nothing"
            GoTo ErrHandling
        End If
    [B]On Error GoTo 0[/B]
   
[COLOR=#ff0000][I] other code...[/I][/COLOR]
   
Exit Sub                'required to hop over error handling if all is OK

ErrHandling:
    MsgBox msg
End Sub

notes
- the code tests for empty table with Debug.Print rather than using IF test which simplifies the coding

This helped me a lot! Thank you so much