我试图改用 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
您的数组类型为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
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句