时间戳

首页 > 解决方案 > 在尝试使代码更快时键入不匹配

excel - 在尝试使代码更快时键入不匹配

问题描述

我试图切换到 For 循环 (For i = 1 To UBound(arrSOPID,1) 以使代码更快；根据我收到的一些建议，在循环许多单元格之前。

然后我在这部分得到类型不匹配...Trim(arrSOPID(i, 1)

   'Store cells in COL A that have values as a range
    Dim arrSOPID As Variant: Set arrSOPID = .Range("A1", .Range("A1").End(xlDown))
    Dim cel As Range

    'Loop through each SOP audit file
    For Each oFile In oFiles
        'Strip audit date out of filename and trim off the file extension
        Dim auditDate As Date: auditDate = CDate(DateSerial(Right(Left(Split(oFile.Name, "-")(3), 8), 4), _
                                                    Left(Left(Split(oFile.Name, "-")(3), 8), 2), _
                                                    Mid(Left(Split(oFile.Name, "-")(3), 8), 3, 2)))
        Dim i As Long: i = 0
        'Loop through all SOP IDs stored in COL A
        For i = 1 To UBound(arrSOPID, 1)
            'MsgBox (Trim(arrSOPID(i, 1)))
            'See if SOP ID in COL A matches SOP ID in Audit file name
            If Trim(RemoveLeadingZeroes(Split(oFile.Name, "-")(2))) = Trim(arrSOPID(i, 1)) Then
                'Insert link to audit, change background color, etc of selected cell
                With cel.Offset(0, 3 + Month(auditDate))
                    .Hyperlinks.Add Anchor:=cel.Offset(0, 3 + Month(auditDate)), Address:=oFile.Path, TextToDisplay:="X"
                    .Interior.Color = RGB(34, 139, 34)
                    .Font.Color = vbBlack
                    .Font.Bold = True
                End With
            End If
        Next i
    Next oFile

标签： excelvba

解决方案

您的数组是Variant. 在String使用Trim(). 像下面这样的东西应该可以工作：

'Store cells in COL A that have values as a range
    Dim arrSOPID As Variant: Set arrSOPID = .Range("A1", .Range("A1").End(xlDown))
    Dim cel As Range
    Dim myStr as String

    'Loop through each SOP audit file
    For Each oFile In oFiles
        'Strip audit date out of filename and trim off the file extension
        Dim auditDate As Date: auditDate = CDate(DateSerial(Right(Left(Split(oFile.Name, "-")(3), 8), 4), _
                                                    Left(Left(Split(oFile.Name, "-")(3), 8), 2), _
                                                    Mid(Left(Split(oFile.Name, "-")(3), 8), 3, 2)))
        Dim i As Long: i = 0
        'Loop through all SOP IDs stored in COL A
        For i = 1 To UBound(arrSOPID, 1)
            myStr = arrSOPID(i, 1)
            'MsgBox (Trim(myStr))
            'See if SOP ID in COL A matches SOP ID in Audit file name
            If Trim(RemoveLeadingZeroes(Split(oFile.Name, "-")(2))) = Trim(myStr) Then
                'Insert link to audit, change background color, etc of selected cell
                With cel.Offset(0, 3 + Month(auditDate))
                    .Hyperlinks.Add Anchor:=cel.Offset(0, 3 + Month(auditDate)), Address:=oFile.Path, TextToDisplay:="X"
                    .Interior.Color = RGB(34, 139, 34)
                    .Font.Color = vbBlack
                    .Font.Bold = True
                End With
            End If
        Next i
    Next oFile

推荐阅读