时间戳

首页 > 解决方案 > VBA 中的类型 13 数据不匹配错误

vba - VBA 中的类型 13 数据不匹配错误

问题描述

下面是我为在 Excel 工作表中搜索评论而编写的代码，评论也包含特殊字符。谁能帮我摆脱类型不匹配错误。下面是我粘贴的代码以供参考

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

标签： vbaexcel

解决方案

问题是Find()有 255 个长度限制

您可以按如下方式解决它：

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

推荐阅读