Converted UDF Formula to VBA but having an Error

  • I have a problem, hopefully someone may be able to help. I have been using MS Office 2016 wherein TEXTJOIN function does not work so i have added EXCEL UDF Function to make it work like TEXTJOIN works.


    Here is the formula


    Code
    1. ConcatStringConditional($EJ$2:$EJ$10000,ER3,$EL$2:$EL$10000)


    I have pasted this formula through VBA but it returns with an error that is #VALUE! I have attached a file below wherein UDF and VBA formula both codes are available.


    The code is extremely slow and is it possible to convert this below range into used range like we mostly used lastfor = Sheet2.Cells(Rows.Count, 148).End(xlUp).Row might speed could be better this way if i am not wrong.

    Code
    1. $EJ$10000


    I will really appreciate the help.

    Files

    • Sheet2.xlsm

      (195.82 kB, downloaded 35 times, last: )
  • It's probably quicker to use arrays for the processing, like this:



    Also note that this does not need to be an array formula.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Thank you very much rory for sure Arrays are better. But second issue is still exists that if i run the Module1 code the result comes with error #VALUE! rather than output.


    I.


  • Try:



    and also remove the Screenupdating commands from the function code.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Glad we could help. :)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why