Looping through a string value based on occurrences of a value and parsing out values into new rows

[moncdanm]

Hello all,

So I'm trying to do something a little bit complex but am not sure how to tackle it as I'm very new to VBA coding syntax (but intermediately experienced in other programming languages)

Basically, I am trying to parse through this text:

<Start>

    <Step>


        <Description>


            This is a description


        </Description>


        <Validation>
    
            This is how we validate
    
        </Validation>


    </Step>


    <Step>


        <Description>


            This is another description.


        </Description>


        <Validation>
    
            And this is another validation.
    
        </Validation>


    </Step>


</End>

And it's parsed based on the key tokens that begin and end the text. So, the beginning statements are brackets followed by the field descriptor and they are thus closed by the brackets and a forward slash.

Right now, I'm using a UDF called Supermid (taken from this website) that returns the values between two given words (in this case, the beginning and end tokens I just described).
So if the text above is in Cell A1, Supermid(A1, "<Validation>","</Validation>") would return "This is how we validate.".

I'm running through a problem where I want to loop through the above text, and parse it seperately for each <Step> instance.

So because there are two <Steps> encapsulating the description token and the validation token, i'd like to seperate them.

So it would look a little something like this:

Step 1	This is a description	This is how we validate
Step 2	This is another description	And this is another validation

<tbody>
</tbody>

Essentially I want to loop through the entire field, and each <Step> that is detected is treated as it's own entity that's iterated upon and is parsed out accordingly to respective columns (col B for the description, col C for the Validation)

I'm thinking the pseudo code would look something like this if i'm incorporating supermid and the value I want to parse is in Cell A1:

For i = 0; i < Steps; i++:
     Range("C1").Formula = "=supermid($A1, ""<Feature Description>"", ""</Feature Description>"")"
     Range("D1").Formula = "=supermid($A1, ""<Feature Validation>"", ""</Feature Validation>"")"]
     Range("B1").Formula = ""Step" & i" //this is to clarify which step number it is

My main issue is converting this psuedo code to VBA syntax, as I've never worked with it to a deep extent and i'm learning as I go.
I'm thinking I have to count the occurrences of <Step>, store that in a Long, and use that as my iterator for 'i'.

The biggest issue is separating the parsed text into new rows for each iteration and I'm not sure how to count the number of Steps that occur in a string value and separate them accordingly.

Is there a way to iterate the Cell number for each loop iteration? And how do I reference that 'i' value when displaying the Steps?

If anyone can point me to the right direction, it would be HUGELY appreciated!

Thank you!!!