excel vba type mismatch- comparing values w/ combination of "right", "offset", "len"

Question

why might the following return a type mismatch error?

Dim Arrow As Workbook
If wb.Name Like "*Arrow*" Then
Set Arrow = wb
dplastrow = activeworkbook.Worksheets(1).UsedRange.Rows(Worksheets(1).UsedRange.Rows.Count).Row
LastArrow = Arrow.ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

For Each account In activeworkbook.Worksheets(1).Range("D11:D" & dplastrow)
For x = 2 To LastArrow
If (Trim(Right(account.Offset(0, 1), Len(account.Offset(0, 1) - 2))) = Arrow.Worksheets(1).Cells(x, "BL")) Then
'some action

I was able to vlookup and return it's matching value in the actual excel gui no problem. I've tried enveloping the cells in cstr(, val( and/or affixing them with .text to no avail.

Do you have a blank cell (or text with a length less than 3)) in the next column that is returning a len of -2 to the Right function? — , May 26 '17 at 13:14
LastArrow = Arrow.ActiveSheet.UsedRange.Rows(**Arrow.**ActiveSheet.UsedRange.Rows.Count).Row — , May 26 '17 at 13:17
you are right sir, thanks! Pertaining to the existence of cells with length<3 — machump, May 26 '17 at 13:19

score 0 · Answer 1 · answered May 26 '17 at 13:31

If you are going to arbitrarily take the length of the value from one cell to the right, you need to ensure that there is sufficient text to subtract 2 from the length.

Dim Arrow As Workbook
If wb.Name Like "*Arrow*" Then
    Set Arrow = wb
    With wb.Worksheets(1)
        lastArrow = .cells(.rows.count, "BL").end(xlup).Row
    End With

    dplastrow = ActiveWorkbook.Worksheets(1).UsedRange.Rows(Worksheets(1).UsedRange.Rows.Count).Row

    For Each account In ActiveWorkbook.Worksheets(1).Range("D11:D" & dplastrow)
        For x = 2 To lastArrow
            If Len(account.Offset(0, 1)) > 2 Then
                If (Trim(Right(account.Offset(0, 1), Len(account.Offset(0, 1) - 2))) = _
                    Arrow.Worksheets(1).Cells(x, "BL")) Then
                    'some action
                End If
            End If
        Next x
    Next account
End If

You should get away from relying on ActiveSheet for worksheet references (see How to avoid using Select in Excel VBA macros) and there are better ways of determining the last row in Arrow's column BL.

excel vba type mismatch- comparing values w/ combination of "right", "offset", "len"

1 Answers1