Macro to copy content between columns based on name of company

Hi, i have little bit harder case and do not know how to solve this...

I have some references number in column AE..not every row has references, there are also blank rows between them...and I need for each references, to check column AD, where are company names as CHANG HWA,CONCORD SECURITIES CORP. etc and for one references there can be more rows with same company name...and I need to copy from column N -ordernumber to column AG for each company...meaning to search all rows and copy all references for one company to one row...For example company CHANG HWA - references are in format 00007654353 00009837308 and i dont need zeros, just follows after them and paste it as 7654353 9837308 for example for company CHANG HWA to one row next to the reference from column AE

I hope its little bit clear what i need to...for every input i will be glad..

Hi,

I hope the top page yellow banner is enough clear …

I uploaded the attachement with coloured examples what should be done....just ignore the names of columns because I erase some columns and left just necessary there..

Hello,

is there any possibility to use this formula into macro? Because I built macro that is deleting some columns, replacing something, copy&paste something and this my thread is another step in the procedure...or is there any other solution,which can be added to my current macro?

Please Login or Register  to view this content.

You can use the function alone as a cell formula.
=JoinIf($B$2:$D$26,3,D2,1," ")
Where:
$B$2:$D$26 as data range.
3 as relative column reference for lookup column within data range.
D2 as lookup value
1 as relative column reference for concatenate range within data range.
" " as a joining string.

it looks ok Jindon,but small adjustment if is possible -

for now, it will give references to each row..but in my excel sheet I need it that -

for example row 13 & 14 CHANG HWA - we can see in column E (Nieuwe referte) that references is only on row 14,row 13 is blank...so I need to get result to column G (refertes) just for the row 14 - 00007351779 00007351806 and leave row 13 in column G blank...is it possible to adjust somehow?

code change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Mannualy entering the formula in the cell:
=If(E2<>"",JoinIf($B$2:$D$26,3,D2,1," "),"")

Hello again, when I tried run your macro, it worked fine in your file Jindon...but when I copy to my macro and did small adjustments, it do nothing...can you please have a look if the code is correct? There are already adjustments according to correct columns in my file + adjustment of code from your last post..

Please Login or Register  to view this content.

Can be problem,that I have opened in the same time two workbooks and both have same sheet -qryFaxTai?

Thanks for cooperation.

That's not "my" file, "your" file originally.

If the data layouts are different from your original file, need to see your true file.

Ok I attached correct file

I don't know what's wrong else, when I run macro, it gave #NAME? error...is there any mistake in formula?

......................

Jindon I dont really know whats wrong...in the example where you added the code it works perfectly, but when I copied to my original macro file, it gives error
I uploaded file with erro, anyway, macro is not saved in this file and also are needed other files, which are connected with whole macro I builted...but I do not think that it should be problem

The error is in the JoinIf function...it gives #Value! error for third and fourth parameter....but it is absolutely same as in your code and your is working...so it seems that problem could be somewhere in my worksheet/workbook?

It will never work on .xlsx file, it should be .xlsm with the code like the file I attached.

ah.....but thats the problem, because main file is every month different, so I cannot have that macro in the file I need, so I have whole macro stored in another excel file and using this macro file to run the macro in xlsx file....Is there any other possibility how to solve this problem? btw. thanks a lot for your help, I appreciate that you have nerves for my problems..

Google

Perosnal.xlsb or Add-In

As you asked in VBA forum, I wrote the code but if you don't like it, just forget it.

I like your code,but I dont get one thing..

why that your code does not work?

For me come email with attachement in .xlsx. I created macro in separate file, code do lot of things - deleting columns,rows,sorting,adding something etc...one part was that contain you create code for....

But in that macro file through Customize Quick Access Toolbar -> Macros and I added this macros here....and whenever I open .xlsx file and run the macro, it works and do all the things and at the end it opens that file with macro.....so code runs fine, but when I added your part, it starts to give errors and I do not know why.....

Why whole macro works fine in xlsx file and your part did not work? whats the difference and whats the solution for that?

No idea how are you calling the sub/function procedure.

If you are applying to the other workbook, you should change the sheet reference to ActiveSheet

Please Login or Register  to view this content.

If you save the workbook with this codes as an add-in, .xlam. and set the reference to it, you can use it all the excel files within your PC.

I solved with add-in and works fine...may I ask, if after End function in this code - can I continue somehow with writing other comands for VBA? or needs to be created new macro?

Hello Jindon,

your formula works fine, but if would be possible small adjustment - the references should be without zeros - 0007752536 should be just 7752536..is it possible? or where in the formula can i change it?

Do you mean?

Please Login or Register  to view this content.

It worked well,but last days there pop up some error message- Object variable or With block variable not set...it seems that some variable is not define? But why it worked fine and not now? I dont get it..

No idea.
I don't think the cause is the function.

If you upload a workbook that doesn't work, it may help.