Combining strings in a range

[sigfreid]

I recently had a query about pulling data from different closed workbooks using a concatenate to easily choose the file:-
http://www.excelforum.com/excel-gene...-workbook.html

With some excellent advice from NVBC I was able to solve it, however I have since found that the time it takes to pull the data especially once it's more than around 1000 records is irritating my boss.

I did some work on some VBA and came up with

Please Login or Register  to view this content.

Which works perfectly and virtually instantly, what I'm trying to do is find a way to make it do

Please Login or Register  to view this content.

Etc or more plainly

A1 = B1 & C1 & D1
A2 = B1 & C1 & D2
A3 = B1 & C1 & D3
A4 = B1 & C1 & D4
A5 = B1 & C1 & D5

Any ideas?

[dominicb]

Good morning sigfreid

Assuming that your range takes up, say, A1:D10, and you want to concatenate B1, C1 and D1 in A1 ... and so on then the code below should work OK. You would need to highlight the range A1:A10 (the code takes from you which range to populate) and then run the macro.

Please Login or Register  to view this content.

HTH

DominicB

Edit : Please remember to use code tags next time you post examples of VBA. I have done it for you on this occasion.

[pike]

or maybe with no loops

Please Login or Register  to view this content.

[sigfreid]

Sorry, always forget the tags lol, unfortunately I can't get either of these solutions to work, the first one returns the error usrcell is an undeclared variable and I don't know what to declare it as.

The second just says type mismatch and gives no more information.

I tried

Please Login or Register  to view this content.

which works,but it seems to create an endless loop and I thought

Please Login or Register  to view this content.

would fix that but it returns an error "Autofill method of range class failed"

[pike]

Hi sigfreid
Place the codes in a module and run the macro code to see results

[sigfreid]

ahaha *feels like a noob* I got yours to work Pike, it gave a "Autofill method of range class failed" at first but I only had to change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

where xx is the last cell I want it to go to.

I think that just about covers it, thanks to you both!

[pike]

I dont understand but any who ...can you please go to your first post select "edit" then "go advanced" and change the sufix to "solved"