SOLVED

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

Copper 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.

@Santhosh_V 

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.

 

Santhosh_V_0-1625142349185.png

 

best response confirmed by allyreckerman (Microsoft)
Solution

@Santhosh_V 

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.

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@Santhosh_V 

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.

View solution in original post