在 vba 宏運行后，只有值應該在單元格中可見。此外，應洗掉所有特殊字符#N/A"。#N/A 所在的所有位置都應為空欄位。
Dim sh As Worksheet, shOld As Worksheet, shNew As Worksheet, lastR As Long, rngB As Range
Dim rngBJ As Range, rngBN As Range, lastR2 As Long, lastR3 As Long, arrVlk, iRow As Long, i As Long, l As Long
iRow = 5 'the row where from the data will be returned
Set sh = Worksheets("PIV Kunde SO & Status")
Set shOld = Worksheets("oldStockAge")
Set shNew = Worksheets("PIV Kunde SO, Vendor & Age")
lastR = sh.Range("B" & sh.Rows.Count).End(xlUp).Row
lastR2 = shOld.Range("B" & sh.Rows.Count).End(xlUp).Row
lastR3 = shNew.Range("B" & sh.Rows.Count).End(xlUp).Row
Set rngB = sh.Range("B" & iRow & ":B" & lastR)
Set rngBJ = shOld.Range("B5:J" & lastR2)
Set rngBN = shNew.Range("B2:F" & lastR3)
For l = 2 To 6
sh.Cells(iRow, l 2).Formula = "=VLOOKUP(B5," & rngBN.Address(external:=True) & "," & l & ",0)"
Next l
sh.Range("D" & iRow, "F" & iRow).AutoFill Destination:=sh.Range("D" & iRow, "F" & lastR)
For i = 7 To 9
sh.Cells(iRow, i 1).Formula = "=VLOOKUP(B5," & rngBJ.Address(external:=True) & "," & i & ",0)"
Next i
sh.Range("D" & iRow, "I" & iRow).AutoFill Destination:=sh.Range("D" & iRow, "I" & lastR)
uj5u.com熱心網友回復：
請在現有代碼的末尾添加下一行：
Dim rngNA as Range
On Error Resume Next 'just to avoid a code error in case of no #N/A return...
set rngNa = sh.Range("D" & iRow, "I" & lastR).SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
If Not rngNA Is Nothing Then rngNA.Value = ""
或者嘗試調整公式以在不匹配的情況下回傳一個空字串，方法如下：
Dim strFormula As String
For l = 2 To 6
strFormula = "VLOOKUP(B5," & rngBN.Address(external:=True) & "," & l & ",0)"
sh.cells(iRow, l 4).Formula = "=If(ISNA(" & strFormula & "),""""," & strFormula & ")"
Next l
strFormula 僅用于避免使用大的“香腸”配方... :)
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/405385.html
標籤：
上一篇：將范圍參考到單元格值