Nested loops and concatenate values

Hi guys,

this one is going to be a challenge:

First some background info:
We're working in a magento2 webshop and every now and then we need to redirect an old page to a new one.
Unfortunately, our magento database contains 10 different webshops and many of the redirects need to be created in every webshop.
The magento tool we are using can only create a redirect for one store at a time.
But there is also an import function that can do the trick at once for all stores.
I'm now looking for a way to quickly generate a redirect import file.

The aim is that the user inputs the breadcrumb (so everything after the main url) of the old and the new url on an imput sheet.
Once he is done adding these breadcrumbs the output needs to be generated.

I will illustrate with screenshots what I plan to do.

Here you see the imput sheet.
The user inputs the breadcrumbs for old and new and then selects in column C the combination of stores these breadcrumbs are valid for.
Workfile.jpg

The user then presses the button to generate the output.
This is where I need code to do the following:

The code needs to:

1. find the first row in "Workfile" where columns A, B and C are populated (and C contains integers only) and
2. store the values on that row as variables: (column A) "OldUrl", (column B) "NewUrl" in (column C) "StoreSelection" and the rownumber as variable "BreadcrumbRow",
3. look up "StoreSelection" on row 1 of the sheet "StoreViews", take the first url underneith that number and store it's value as variable "WebshopUrl".
   StoreViews.jpg
4. activate sheet "NewRedirects" and find the first empty row.
5. set value in column A of that row to (concatenate "WebshopUrl" with "OldUrl")
6. set value in column B of that row to (concatenate "WebshopUrl" with "NewUrl")
7. Go back to "StoreViews" and check if there is another "WebshopUrl". If there is: repeat steps 3-6, if not, go to step 8.
8. Go back to "Workfile" and find next row that complies with step 1 conditions. If there is: repeat steps 2-8, if not, end sub.

The following image shows what the output would be in "NewRedirects" if there would only be one line (line 6) in "Workfile"
NewRedirects.jpg

Can anybody help me with that?

Hi All,

Reading my question explanation and realising that I pretty much described the steps to follow, I decided to start tinkering with a bit of code myself.
I got it to work up to the moment when I need to repeat my step (the nested loops)
So the code right now, adds redirects the way I want for one line. (The concatenate stuff has been solved).

Now I need to define a loop in which first we check if there are more StoreUrls in the list, and if so, set the StoreUrlRow to "StoreUrlRow+1" and consequently reset OldUrl and NewUrl and StoreUrl and add the next redirect.
When all redirects of that particular url have been set, check to see if there are more urls to redirect, so to set BreadcrumbRow to "BreadcrumbRow+1", StoreUrlRow back to "2" and start again.

For this I would probably have to modify BreadcrumbRow and StoreUrlRow to ranges... but I'm not sure how.
I have uploaded a new version of the spreadsheet and hope to get some pointers on how to proceed.

Please Login or Register  to view this content.

Hi John,

Thank you very much!
That first one works!

I don't understand much of the code yet.... intrigued.

The second one works aswell, but I will go with the first. It's minimalistic and efficiënt.

Update:
I think I now understand it's workings, but I'm affraid I will never be able to write something like this myself.
At least I can copy it now for other purposes.

I'm afraid I will never be able to write something like this myself.

Yes you will ! with a little practice. Look on the Internet for VBA array processing or similar. Array processing is much faster than read/writing to the worksheet,

The code from Artik uses the Scripting Dictionary which is also very fast, again using arrays.

https://excelmacromastery.com/excel-...0at%20a%20time.