Jul 01 2021 03:03 AM
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.
Jul 01 2021 03:19 AM
Please attach two sample workbooks.
Jul 01 2021 03:35 AM
@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.
Jul 01 2021 04:44 AM
See workbook2 (now a macro-enabled workbook)
Jul 01 2021 05:26 AM
@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.
Jul 01 2021 05:30 AM
SolutionI 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.
Jul 01 2021 06:19 AM
Jul 01 2021 05:30 AM
SolutionI 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.