Loops and Arrays

**v2jtb** · 01-29-2010, 10:28 AM

Hi I have an XML page for my home web page that has the following lines:

<image>
<filename>Wedding_Pictures_001.jpg</filename>
<caption>Wedding Pictures 001</caption>
</image>

I change the Gallery from time to time and at the last count I had 471 images.
I have concatenated though a spread sheet for some time now and it has been very time consuming.
So I started to look at recording a macro and have found the process intriguing and very frustrating due to my limitations.
What I want to do is run the image and caption numbering from _001 to _471. I think this would be simple enough but the books I have been reading are way too advanced.

Can anyone help?

**StephenR** · 01-29-2010, 10:43 AM

I'm none the wiser. Post a sample workbook which illustrates what you want doing.

**v2jtb** · 01-29-2010, 10:57 AM

Hi sorry for not explaining myself properly.

I want to duplicate the text below from 001 to 471.

<image>
<filename>Wedding_Pictures_001.jpg</filename>
<caption>Wedding Pictures 001</caption>
</image>
<image>
<filename>Wedding_Pictures_002.jpg</filename>
<caption>Wedding Pictures 002</caption>
</image>

Down to
<image>
<filename>Wedding_Pictures_471.jpg</filename>
<caption>Wedding Pictures 471</caption>
</image>

**StephenR** · 01-29-2010, 11:00 AM

Yes, I understand, but can you post a small workbook showing your data? That way any response will work for your situation.

EDIT: or you could try this. Replace the second line with this

="<filename>Wedding_Pictures_"&TEXT((ROW()+2)/4,"00#")&".jpg</filename>"

and the third line with this

="<caption>Wedding Pictures "&TEXT((ROW()+1)/4,"00#")&"</caption>"

Select the first four rows and then drag down.

**v2jtb** · 01-29-2010, 11:07 AM

Sorry I don't know how to post the workbook?

**v2jtb** · 01-29-2010, 11:17 AM

Hi I have worked it, sorry new on the forum.

I want to be able to add say 500 to the code and for the VBA or macro to run down the list?

I hope this helps

**StephenR** · 01-29-2010, 12:00 PM

Did you try my suggestion?

**v2jtb** · 01-29-2010, 12:09 PM

Hi StephenR,

Yes that would do the tick...and thank you for your help.
I am sorry as I want to learn about loops and arrays I know it is using a hammer to crack a nut.

I am trying to learn how this could be done and am struggling a little on the macro side