Halp! Read cell for seperator, copy text, paste each segment to new workbook cell.

[youngtusk87]

Hey all, I hate to be that guy that joins a forum and immediately posts a problem but I've been researching this issue for a few days now and can't figure it out.

The user will enter image URLs into a single cell in a "Master" workbook. If there are more then one URL they must be separated by commas(,). When they hit the compile data macro button, I need it to 1) check if there is more then one URL, 2) (Assuming there is more then one URL) copy all text in the Master cell up to each comma, and 3) paste that text segment into incrementing cells in another workbook(the first string goes to column BE, the second to BF, etc, up to a max of six to BJ). This must be done for every cell in the column.

Thoughts?

EDIT: Thanks to Leith, this has been solved, here is the final code:

Please Login or Register  to view this content.

[Leith Ross]

Hello youngtusk87 ,

Welcome to the Forum!

Which rows does the data start in for the source workbook and the destination?
What are the names of the sheets?

[youngtusk87]

Thanks for the welcome message!

As it is now the row for the source and destination workbooks both start at 2.

The source workbook is called "Master_Template" and the destination is "Sears_Addon_Template"

Thanks!

[Leith Ross]

Hello youngtusk87,

Is there only one worksheet in each workbook?

[youngtusk87]

Ah ha, kind of important piece of information huh?

For the Master/source workbook the data will be in the first sheet, to be named "Master Sheet". Sheet 2 is reserved for instructions.

The Sears/destination workbook uses the second sheet called "Data Format" for it's data.

Thanks again for the timely responses!

[Leith Ross]

Hello youngtusk87,

Sorry about the delay, my computer decided it need a rest and crashed. Try this macro and let me know the results.

Please Login or Register  to view this content.

[youngtusk87]

Edit:
Okay, so it works, kind of!

It pastes the info in the right place and the right format, but with row it compiles one less URL. So from A2 it pastes all the URLs, then for A3 it pastes 5/6, then A4 it pastes 4/6, etc.

It also gives me a Runtime error '1004', "Application-defined or object-defined error" and when i hit debug, it highlights this sepcific piece:

Please Login or Register  to view this content.

[Leith Ross]

Hello youngtusk87,

It will be easier to troubleshoot if you can post a copy of the workbook.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[Leith Ross]

Hello youngtusk87,

It will be easier to troubleshoot if you can post a copy of the workbook.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[youngtusk87]

Upon further testing, I have concluded that it works (almost) perfectly!

The only thing that doesn't quite fit is that the last URL in the list may or may not have a comma at the end of it(due to user discretion). If it doesn't have a comma at the end, the macro doesn't copy and paste the last URL. Is there an easy way to fix this?

Thanks again so much, very exciting

[Leith Ross]

Hello youngtusk87,

That was my fault, The Split method returns a zero based 1-D array, i.e. the first element is (0). Ranges are one based 2-D arrays whose first element is (1, 1). To fix the problem, a 1 needs to be added to the UBound(URLs).

Revised Code:

Please Login or Register  to view this content.

[youngtusk87]

When I replaced that line with the revised code I receive an error message saying that it expects an equal sign where the + is.

[Leith Ross]

Hello youngtusk87,

Again, it is my fault. My typing skills are not very good. the +1 needs to be inside the parentheses like this...

Please Login or Register  to view this content.

[youngtusk87]

Oh, please! I can program my object-oriented code enough but this sort of stuff is so beyond me at this point! I appreciate your help.

The syntax error is gone! However, it now tells me at run-time that "Object doesn't support this property or method... " and in debug it highlights the UBound line of code. I suppose it is referring to the .Values call..?

[Leith Ross]

Hello youngtusk87,

This is the worst day I have had all year. I can't get anything right! I can't type and I can't see my typing mistakes. It should be Value not Values.

Please Login or Register  to view this content.

[youngtusk87]

Hey! Sorry it has been so long since I responded, and I don't mean to bump but I did want to say thank you so much for your help! It works perfectly! Now I just need to integrate it into a much larger macro... I might come back for more help later. Thanks again Leith.

[youngtusk87]

Sorry for a double post, but I have another hurdle to jump regarding this issue.

The code works great, but I need to do a similar copy/paste to yet another workbook. This time, however, the first URL goes into a cell in column J(starting at J2) on a "Feed Columns" sheet of the "Buy_Addon_Template" workbook, and the rest of the URLs are copied into a single cell in column K(starting at K2) and each comma replaced by a pipe character "|" so that the URLs are separated by each other with the "|" character.