How to make a string accept an Excel error- runtime error 13- mismatch

I am trying to format downloaded data and then insert into SQL. The nature of the data is such that there will be errors such as #NAME?, #N/A Invalid Field etc. I have a variable of data type string which takes the value of a new cell each time a for loop is run. The loop crashes due to a mismatch error occuring when the loop hits a data point with an error in it (found out my message boxing the loop).

Why can a string not accept an error as its value for the string? I have seen a post about this and the recommended answer was to use If IsError but I need the '#N/A' part of the error as to identify that one has occured, rather than skip the row. Any suggestions would be greatly appreciated! Thanks :) An Extract of the code is below:

'Insert static fields
Dim field As String

        code = "'" & tbl.DataBodyRange(i, 1) & "'"
        For j = 2 To 48

            MsgBox (tbl.DataBodyRange(i, j))
            field = tbl.DataBodyRange(i, j).Value
            'Recursively going through each cell

            If InStr(field, "#N") <> 0 Then
                field = "NULL "
                k = 1

It hits the error at the messagebox first, the message box will display the mismatch error.

Solution

One way around the problem is to use the VARIANT data type to deal with the error.

As you've found out a STRING variable can't hold an error value, while the VARIANT can. You can then use code along the lines of:

IF ISERROR(field) THEN
   field = NULL  
   K=1  
END IF

A VARIANT can also hold a NULL value.

Edit:

I have seen a post about this and the recommended answer was to use If IsError but I need the '#N/A' part of the error as to identify that one has occured, rather than skip the row.

The ISERROR part identifies that an error has occurred. This line of code on its own won't skip the row; it gives you an opportunity to do something else with the row such as replace the error value with a NULL value or some other required value.