Vlookup匹配日期格式的数据时得到的是数值

金樽邀月

    各位大师，我用Vlookup匹配日期格式数据时得到的是数值，无法完成对比，请教高手如何解决。

taller

1. Sub 对比()
   
2. Worksheets("人事报表").Activate
   
3. h = 2
   
4. Do While Cells(h, 2) <> ""
   
5.       Cells(h, 8) = Application.VLookup(Cells(h, 2), Sheets("法务报表").Range("B:F"), 2, 0) & Application.VLookup(Cells(h, 2), Sheets("法务报表").Range("B:F"), 3, 0) & Application.VLookup(Cells(h, 2), Sheets("法务报表").Range("B:F"), 4, 0) & Application.VLookup(Cells(h, 2), Sheets("法务报表").Range("B:F"), 5, 0)
   
6.       Cells(h, 9) = Cells(h, 3) & Cells(h, 4) & CDbl(Cells(h, 5)) & CDbl(Cells(h, 6))
   
7.       Cells(h, 7) = "=Exact(H" & h & ",I" & h & ")"
   
8. h = h + 1
   
9. Loop
   
10. MsgBox "对比完毕！"
    
11. End Sub
    

复制代码

金樽邀月

taller 发表于 2024-11-15 03:07

谢谢老师，问题完美解决。

翁知江安

Sub 对比()
Dim i, k, m, s, t, irow, irow1
Dim arr, brr, crr
irow = Sheets("法务报表").[a65536].End(xlUp).Row
arr = Sheets("法务报表").Range("a1:f" & irow)
Dim d As Object
Set d = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
    d(arr(i, 2)) = arr(i, 3) & "," & arr(i, 4) & "," & arr(i, 5) & "," & arr(i, 6)
Next
irow1 = Sheets("人事报表").[a65536].End(xlUp).Row
brr = Sheets("人事报表").Range("a1:I" & irow)
ReDim crr(1 To irow1 - 1, 1 To 6)
For k = 2 To irow1
   For m = 1 To 4
    crr(k - 1, m) = Split(d(brr(k, 2)), ",")(m - 1)
Next
   s = CStr(crr(k - 1, 3))
   t = CStr(crr(k - 1, 4))
   crr(k - 1, 6) = crr(k - 1, 1) & crr(k - 1, 2) & s & t
   If crr(k - 1, 6) = brr(k, 9) Then
      crr(k - 1, 5) = "相符"
     Else
      crr(k - 1, 5) = "不相符"
   End If
Next
Sheets("人事报表").[c2].Resize(UBound(crr), 6) = crr
MsgBox "ok"
End Sub

翁知江安

仅供参考。。。。