Forum Discussion

hrh_dash's avatar
hrh_dash
Iron Contributor
Sep 20, 2022

Re: Alternative macro to replace multiple for loops in vlookup to enable code to execute faster

, somehow the Destwb was populated as 0, when search result (DC2, DC1, MISC and LOD1) is not found in Destwb after executing the code line by line.

4 Replies

    • hrh_dash's avatar
      hrh_dash
      Iron Contributor
      Oct 25, 2022

      HansVogelaar , i would like to insert an IFNA function together with the WITH FUNCTION.

       

      i tried the following but no values were populated. Therefore, how can i insert the IFNA function?

              With Destws.Range("O2:O" & DestwslastRow)
        .NumberFormat = "dd-Mmm-yy"
        .Formula = "IFNA(VLOOKUP(A2," & wsrng.Address(External:=True) & ",7,FALSE),"")"
        .Value = .Value
        End With

       

       

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Oct 25, 2022

        hrh_dash 

        A formula should begin with =, and quotes within a string should be doubled:

                .Formula = "=IFNA(VLOOKUP(A2," & wsrng.Address(External:=True) & ",7,FALSE),"""")"

         

Resources