VBA 保留原格式

ljc6

sheet1.range("a1).resize(UBound(arr),9) = arr   执行完了以后，C列 原本的文本格式变成了数字格式怎么样才能保留原格式呢

凌空一羽

加个val就行了。见附件

ljc6

凌空一羽 发表于 2018-8-9 14:56
加个val就行了。见附件

可以了，学习了，非常感谢谢!

ljc6

ljc6 发表于 2018-8-10 15:49
可以了，学习了，非常感谢谢!

Sub vlookup()

Dim i, j, k, dic As Object, arr, brr, crr

Set dic = CreateObject("scripting.dictionary")


brr = Sheet2.Range("a2").CurrentRegion
crr = Sheet3.UsedRange
arr = Sheet1.Range("a4").CurrentRegion
For i = 2 To UBound(brr)
dic(brr(i, 2)) = brr(i, 6)
Next i
For k = 2 To UBound(crr)
dic(crr(k, 1)) = crr(k, 4)
Next k

For j = 2 To UBound(arr)
arr(j, 9) = dic(Val(arr(j, 3)))
Next j
Sheet1.Range("a4").Resize(UBound(arr), 9) = arr


End Sub

ljc6

凌空一羽 发表于 2018-8-9 14:56
加个val就行了。见附件

Sub vlookup()

Dim i, j, k, dic As Object, arr, brr, crr

Set dic = CreateObject("scripting.dictionary")


brr = Sheet2.Range("a2").CurrentRegion
crr = Sheet3.UsedRange
arr = Sheet1.Range("a4").CurrentRegion
For i = 2 To UBound(brr)
dic(brr(i, 2)) = brr(i, 6)
Next i
For k = 2 To UBound(crr)
dic(crr(k, 1)) = crr(k, 4)
Next k

For j = 2 To UBound(arr)
arr(j, 9) = dic(Val(arr(j, 3)))
Next j
Sheet1.Range("a4").Resize(UBound(arr), 9) = arr


End Sub