Looping through a string value based on occurrences of a value and parsing out values into new rows
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:
<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>
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> </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
Essentially I want to loop through the entire field, and each 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 Beginning
<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!
Looping through a string value based on occurrences , Hello all, So I'm trying to do something a little bit complex but am not sure on occurrences of a value and parsing out values into new rows. will loop through each letter in the string. Loop Through Each Character in a String – Alternative. Read Every Character in a String: This example reads every character in a string from left to right and returns the result in a message box. It makes use of the Mid function.
Sub Tester() 'in your VBProject, add a reference to: ' "Microsoft XML v6.0" Dim steps As Object, step As Object, i As Long Dim oXMLDoc As New MSXML2.DOMDocument60, xmlData 'XML needs a root element xmlData = "<?xml version=""1.0""?><data>" & ActiveSheet.Range("A5").Value & "</data>" oXMLDoc.LoadXML xmlData oXMLDoc.setProperty "SelectionLanguage", "XPath" Set steps = oXMLDoc.SelectNodes("//data/Step") For Each step In steps i = i + 1 Debug.Print "****** Step" & i Debug.Print "Description", step.SelectSingleNode("Description").nodeTypedValue Debug.Print "Validation", step.SelectSingleNode("Validation").nodeTypedValue Next step End Sub
I'm storing your data in a worksheet cell, but it could come from anywhere else.
Loop Through a String - VBA Code Examples, The following is an example of looping through a string using a For…Next Loop, and returning each character in a msgbox. How to loop through rows until a specific value found in Excel? In some cases, you may want to loop through rows one by one until a specific value found in a sheet. Here I introduce a macro code to help you to loop data until a specific value met.
So I opted not to go the XML route (as that wasn't my intention when using those types of tags, it was more just a convention I was familiar with in HTML) and instead just went a sorely VBA route.
Here's what I came up with:
Dim cell As Range Dim rng As Range Dim Jdescription As String Dim steps As Integer Dim name, desc, valid As Integer Dim newDescription As String name = 1 desc = 1 valid = 1 Set rng = Sheets("Scratch2").Range("A1:A" & LastRowDescription) For Each cell In rng.Cells Jdescription = cell.Value steps = (Len(Jdescription) - Len(Replace(Jdescription, "<Step>", ""))) / Len("<Step>") For i = 1 To steps newDescription = Left(Jdescription, 7 + InStr(Jdescription, "</Step>")) Sheets("Scratch2").Cells(name, 2).Value = "Step " & i name = name + 1 Sheets("Scratch2").Cells(desc, 3).Value = SuperMid(newDescription, "<Description>", "</Description>") desc = desc + 1 Sheets("Scratch2").Cells(valid, 4).Value = SuperMid(newDescription, "<Validation>", "</Validation>") valid = valid + 1 Jdescription = Replace(Jdescription, newDescription, "") Next i Next cell
StrSplit() / StringSplit, The StrSplit function and StringSplit command separate a string into an array of Separates a string into an array of substrings using the specified delimiters. For example: "," would divide the string based on every occurrence of a comma. is in standard CSV (comma separated value) format, use a parsing loop since it� Loop 5 (VBA For Loop in Reverse with STEP Instruction) It is not necessary that counter in the For loop will only move from low to higher values; instead, For loop can run backwards, too i.e. high to lower values. Even though the Step value is forward 1 by default, however, it can be set to a number in reverse order.
7. Strings — How to Think Like a Computer Scientist: Learning with , Depending on what we are doing, we may want to treat a compound data of a string, you just put 0, or any expression with the value 0, in the brackets: Each time through the loop, the next character in the string is assigned to the variable char. The solution here is to concatenate a new first letter onto a slice of greeting. The idea is to loop trough the table rows ordered by the CursorTestID column and update the RunningTotal column with the sum of the CursorTestID column value and the value of the RunningTotal column of the previous row. But before starting, first we need to generate some test rows with the next script.
Helpful Python Code Snippets for Data Exploration in Pandas, For anyone new to data exploration, cleaning, or analysis using count the number of occurrences of each value df[df.column_y == “string_value”]. column_z 1000 #can create new columns without for loops# removing columns rows in position 0/1/2, all columns#filtering out and dropping rows based� Value: Click in the “Enter variable value” field and in the “Dynamic content” tab to the right, click on “Email Body”(or whatever you have named the field in the Email Source list) within the “Get items from Email Sources to get values” section.
In the second loop we print out the array and you can see that none of the values have changed. When we use the For Loop we can change the array item. If we change the previous code to use the For Loop you it will change all the array values to “Z”
- This looks like XML, so it seems like you'd be better off using the standard approaches for working with XML, rather than using a string-based approach?
- to @TimWilliams comment, use the XML DOM Document in excel. You can loop through nodes very easily with this.