Type 13 Data mismatch error in VBA

Question

Below is the code which i gave written to search for the comment in the excel worksheet, the comments contains special characters too. can anyone help me to get rid of the type mismatch error. Below it the code which i am pasting for the reference

Option Explicit
 Sub Match_ProjCode()
 Dim CSAT_Comments As Workbook
 Dim comment As Worksheet
 Dim matchcomment As Worksheet
 Dim comment_string As String 'To store the comment
 Dim Column As Integer
 Dim Row As Integer
 Dim match_Row As Integer
 Dim comments_Column_Name As String '
 Dim Comments_Column_Value As String 
 Dim Comments_ProjCode As String 'To store the project code
 Dim RangeObj As Range

Set CSAT_Comments = ActiveWorkbook
Set comment = CSAT_Comments.Worksheets("Qualitative Analysis_2018 Cycle") ' 
Set matchcomment = CSAT_Comments.Worksheets("Consolidated Comments") '

Dim range1 As Range
Dim rng As Range
matchcomment.Range("A2").Select

Set range1 = matchcomment.Range(Selection, Selection.End(xlDown)) 



For Each rng In range1.SpecialCells(xlCellTypeVisible)

comment_string = rng.Value ' Comment text will be stored
match_Row = rng.Row 'comment row will be stored

With comment
.Activate
Columns("AK:BL").Select

      Set RangeObj = Selection.Find(What:=comment_string, After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False) ' to search for the comment in the comment worksheet

    If Not RangeObj Is Nothing Then               

    .Range(RangeObj.Address).Select 'Select the cell of the searched value
    Column = ActiveCell.Column 'Get the column number of the searched value
    Row = ActiveCell.Row ' Get the row number of the searched value

    comments_Column_Name = Split(Cells(, Column).Address, "$")(1) ' Trim the column name from the cell address
    Comments_Column_Value = .Range("" & comments_Column_Name & 1) ' Get the comment heading
    Comments_ProjCode = .Range("A" & Row) 'Get the project code

             With matchcomment
             .Activate
             .Range("C" & match_Row) = Comments_Column_Value ' Paste the comment heading name in the match sheet
             .Range("D" & match_Row) = Comments_ProjCode 'Paste the project code in the match sheet
            End With
    Else
   End If

End With
Next rng
End Sub

`Dim comment_string As String` instead of `Variant`? Would this work? Then `Dim RangeObj as Range`. — Vityata, May 17 '18 at 11:54
earlier i has declared it as the string. It worked for the below comment value - **Overrall experience is at good level. Although at different stages of project the experience was different - from somewhat satisfied till the end** And it didn't work for the below comment Value- **The/.. team is very talented and they work hard.I appreciate the team's efforts. They have worked many weekends and evenings. The team has had changes to make and with little notice. These teams are not large but they collaborate and get the work done with professionalism and good attitude** — Karthik P B, May 17 '18 at 11:55
why i have specified the comments here is to bring the clarity, the above suggested solution is working for the first comment which i have mentioned but not for the second — Karthik P B, May 17 '18 at 12:04
Have you tried to write `Dim RangeObj as Range` in your code? — Vityata, May 17 '18 at 12:07
Yes, i made the changed now, but still the same run time error — Karthik P B, May 17 '18 at 12:08
@KarthikPB Can you please [edit] your question with the code you actual have now, after using `Option Explicit`? — Pᴇʜ, May 17 '18 at 12:15
This is not your actual code `CSAT_Comments` is not declared. This must throw a "variable not declared" error at first. Please make sure the code in the question is **exactly** the same you use. Best copy and paste it. • Also please don't name a variable `Match` this is a function name used by Excel and might cause strange effects. — Pᴇʜ, May 17 '18 at 12:21
Now there is no `Sub` in your code. Please check again, if we don't have the exact code we cannot help. — Pᴇʜ, May 17 '18 at 12:25
Like @Vityata already said this should work. Also I cannot reproduce the error if I create these sheets and run your code it just runs fine. Check the values of your variables when the error occurs, and check if there is something different in your data at the point of error. • And you might have a look here [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) to make it more stable and faster. — Pᴇʜ, May 17 '18 at 12:38

score 1 · Accepted Answer · answered May 17 '18 at 12:54

the issue is that Find() has a 255 length limit

you could get around it as follows:

For Each rng In range1.SpecialCells(xlCellTypeVisible)

    comment_string = Left(rng.Value, 255) ' <<<<Comment text will be stored up to 255 length
    match_Row = rng.Row 'comment row will be stored

    With comment
        .Activate
        Columns("AK:BL").Select

        Set RangeObj = Selection.Find(What:=comment_string, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False) ' to search for the comment in the comment worksheet

        If Not RangeObj Is Nothing Then
            If RangeObj.Text = rng.Value Then '<<<< be sure the whole text matches

                .Range(RangeObj.Address).Select 'Select the cell of the searched value
                Column = ActiveCell.Column 'Get the column number of the searched value
                Row = ActiveCell.Row ' Get the row number of the searched value

                comments_Column_Name = Split(Cells(, Column).Address, "$")(1) ' Trim the column name from the cell address
                Comments_Column_Value = .Range("" & comments_Column_Name & 1) ' Get the comment heading
                Comments_ProjCode = .Range("A" & Row) 'Get the project code

                With matchcomment
                    .Activate
                    .Range("C" & match_Row) = Comments_Column_Value ' Paste the comment heading name in the match sheet
                    .Range("D" & match_Row) = Comments_ProjCode 'Paste the project code in the match sheet
                End With
                Else
            End If
        End If

    End With
Next rng

Thanks a lot @DisplayName , now it is working fine. I didn't notice the length limit for the find functionality. Thanks once again :) — Karthik P B, May 18 '18 at 05:59

Vityata · Answer 2 · 2018-05-17T12:12:52.013

0

When you are not using Option Explicit and this is where the problems start. The RangeObj is not declared, hence VBA "declares" it as a Variant. However, it should be at least an Object and an Object of type Range, if possible.

Thus, to make sure that the code works further, declare the RangeObj explicitly like this:

Dim RangeObj as Range

To make sure that every variable is declared explicilty, write Option Explicit on the top of the module.

Option Explicit MSDN Reference

edited May 17 '18 at 12:12

answered May 17 '18 at 11:58

Vityata

42,633
8
55
100

i declared the RangeObj as Range and added Option Explicit, but still i am getting the run time error. – Karthik P B May 17 '18 at 12:11
@KarthikPB - This is strange - can you write `Option Explicit` on the top of the module and try to declare any variable, which VBA is asking for, once you click `Debug>Compile`? https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/option-explicit-statement – Vityata May 17 '18 at 12:13

Type 13 Data mismatch error in VBA

2 Answers2