How to fix Left, Mid problems? I get error "Type Mismatch 13" on the line with Left

Question

I'm reading strings from cells and separate them by the beginning of their name. I'm using Left function and compare it to beginning, but I get

Error 13 "Type Mismatch"

I found something on the internet and here like VBA Left(str, 2) but it doesn't work either.

Sub start()

Dim strprj As String
Dim LastRow, radek As Long

LastRow = Worksheets("PROJECT_OFFERSNDA").Range("A1").CurrentRegion.Rows.Count
For radek = 0 To LastRow - 1
    strprj = Worksheets("PROJECT_OFFERS-NDA").Range("A1").Offset(radek)
    If Left(strprj, 2) = "\\" Then
        MsgBox Str
    End If
Next radek
End Sub

Answer 1

You can't get a type mismatch on the Left function call with that code, because strprj is guaranteed to be a String:

Dim strprj As String

If you're getting a type mismatch error, it must be on this line:

strprj = Worksheets("PROJECT_OFFERS-NDA").Range("A1").Offset(radek)

Because that instruction is reading a Variant and implicitly coercing it into a String - this may succeed if the cell contains any value that can be represented as a string, or fail with a type mismatch otherwise.

That would happen if the cell you're reading from contains an error value, e.g. #VALUE!, #NA, or #REF!.

The solution is to avoid reading cells into anything other than a Variant.

Dim cellValue As Variant
cellValue = Worksheets("PROJECT_OFFERS-NDA").Range("A1").Offset(radek).Value

And then to validate what we're looking at:

If Not IsError(cellValue) Then
    strprj = Left(cellValue, 2)
    '...
End If

There are other issues:

LastRow = Worksheets("PROJECT_OFFERSNDA").Range("A1").CurrentRegion.Rows.Count

This isn't necessarily reliable. First, you don't want to be dereferencing that sheet every time you need it. Pull it into a local variabel:

Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("PROJECT_OFFERSNDA") '<~ ThisWorkbook? another workbook? Be specific!

This is also a potential issue:

Dim LastRow, radek As Long

radek is a Long integer, ....and LastRow is an implicit Variant/Empty. And, you don't need radek for another couple of lines, so, declare it when you need it. Getting the last row is usually more reliable with Range.End, used from the bottom of the sheet:

Dim lastRow As Long
lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

Dim radek As Long '<~ what's wrong with "currentRow"? why "radek"?
For radek = 0 To lastRow - 1
    '...
Next

That loop is confusing. Range.Offset is confusing. You need to know which row to look at to debug and make sure the data is ok, but nothing in your code points directly to that row number.

Dim currentRow As Long
For currentRow = 2 To lastRow

    Dim cellValue As Variant
    cellValue = ws.Cells(currentRow, 1).Value

    If Not IsError(cellValue) Then

        Dim projectPath As String '<~ "strprj" wants more vowels, and "str" prefix is irrelevant!
        projectPath = cellValue

        If Left(projectPath, 2) = "\\" Then
            MsgBox projectPath
        End If

    Else
        Debug.Print "Row " & currentRow & " contains an error, cannot process."
    End If
Next