Convert formulas to values and remove special characters-CodePudding

After the vba macro is running only the values sholud be visible into the cells. In addtional all special character #N/A" should be removed. Everywhere where #N/A stands should then be an empty field.

    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)

CodePudding user response：

Please, add to the end of your existing code the next lines:

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 = ""

Or try adapting the formula to return an empty string in case of no match, in the next way:

    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 is used only to avoid a big 'sausage' formula... :)