Need VBA code to get the data from another excel and use few formula, write the output back to excel

Occasional Contributor

Hi All,


I have a monthly data in a  excel workbook named as workbook1, need a VBA code, put it into a button and placed in workbook 2. And by using the code to get the data of one column (named as A) from the  Workbook 1 and use some formula , then write the output back to the workbook1 into a column (named as B)


Formula that was using in this scenario : =TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH({"Generic Mailbox","Distribution","Non-standard","NSSR","CASL Block","Virtual phone ","unsatisified"},A1)),{"Shared Mailbox","DL ","NSSR Request","NSSR Request","O365 block List","Vioce Mail ","Unsatisfied Requests"},""))


I have tried but it not accomplish the goals. So Anyone could help on this issue.


@Hans Vogelaar, If possible could you look into this scenario.


Thank you.


6 Replies

@Hans Vogelaar , Please find the sample workbook 1 in the attachment and the 2nd workbook which is an empty workbook. In that need to write a VBA, then put it into button. The function is whenever we click the button in the excel workbook 2 , it will get the column A data from Workbook 1 and process the  formula that you have shared previously, then write the data back output to Workbook 1 in Column B. 


Thanks for your help on this.


See workbook2 (now a macro-enabled workbook)

@Hans Vogelaar  Thank you so much for your Prompt help on this scenario.


After  I clicked the button its working but the same output is pasted in the workbook2 as well like the below screenshot.

Is there any way to get rid of them.





I did what you asked, but I already suspected that it wasn't what you wanted.

Remove the lines

        ' Optional
        ws2.Range("A2:A" & lr).Value = .Value

from the code.