Excel VBA Type Mismatch referencing Cell.Value

Question

Hi sorry this is almost certainly going to be a "doh" moment but I haven't coded for a long time and I'm a bit rusty.

I have a cell value which I keep getting a type mismatch when I try and treat it as a string, yet CStr and testing for IsNull etc don't seem to help. Grateful for some advice.

Set ClientTable = SourceBook.Sheets("Source Data").Range("extdata")
For Each rng1 In ClientTable.Columns(1).Cells
    'if not first row (header row) and the customer name is matching the selected customer
    If (i <> 0) And (rng1.Value = SourceBook.Sheets("Source Data").Range("C1")) Then
        If Not IsNull(ClientTable.Columns(6).Cells.Offset(i, 0).Value) Then
            MsgBox ClientTable.Columns(6).Cells.Offset(i, 0).Value ' type mismatch here
        End If
        With Sheets("Contacts").Range("A1")
            .Offset(rowToWriteTo, 0).Value = ClientTable.Columns(6).Cells.Offset(i, 0).Value ' first name
            .Offset(rowToWriteTo, 1).Value = ClientTable.Columns(2).Cells.Offset(i, 0).Value ' last name
            .Offset(rowToWriteTo, 5).Value = ClientTable.Columns(3).Cells.Offset(i, 0).Value ' email
            .Offset(rowToWriteTo, 6).Value = ClientTable.Columns(4).Cells.Offset(i, 0).Value ' DDI
            .Offset(rowToWriteTo, 7).Value = ClientTable.Columns(7).Cells.Offset(i, 0).Value ' mobile
            .Offset(rowToWriteTo, 8).Value = ClientTable.Columns(5).Cells.Offset(i, 0).Value ' title
        End With
        rowToWriteTo = rowToWriteTo + 1

    End If
    i = i + 1
Next

If I remove this bit of code the Sheets("Contacts").Range("A1") assignments all work fine.

PS: use of MsgBox is just for debugging. Trying to assign ClientTable.Columns(6).Cells.Offset(i, 0).Value to a string variable produces the same error.

Many thanks in advance for any assistance!

Fratyx Fratyx · Accepted Answer · 2015-09-04T11:31:44

The expression ClientTable.Columns(6).Cells.Offset(i, 0).Value results in an array of values because Columns(6) is a Range object with multiple cells. You can't assign this array to a string. Maybe you should use ClientTable.Cells(i, 6).Value instead. So you get a single value.

Excel VBA Type Mismatch referencing Cell.Value

1 Answers