Adding lines ABOVE based on number of commas used.

Hello LikeSeeker,

The macro below has been added to a button on the "Orig" worksheet in row 1. This add a new row for each comma found plus one. One is added to the comma count since there are n + 1 items separated by the commas.

Please Login or Register  to view this content.

Hi Leigh,

Thanks very for taking the time coding it.

It seems the entire code revolves around the "RegExp", and I am always intrigued by it

Please Login or Register  to view this content.

Is it a free object that we can use in VBA library?

and what does the

Please Login or Register  to view this content.

do? It goes back one row?

What does

Please Login or Register  to view this content.

do? Specifically, what are the indexes 1 and 3 referring to?

Hello LifeSeeker,

The Regular Expressions object library was added to VBScript in 2000. This powerful pattern matching script is used by several other languages to parse web data. Languages like Java, PHP, and ASP to name a few. The down sides are the lack of explicit feature documentation and the strange syntax.

The backslash "\" is used to stop Regular Expressions from interpreting a character in a string that has special meaning in Regular Expressions. In this case, that character is a comma. The Global property is set to true to match all patterns in the text. By default this is set to False, which then only searches for the first match in the text. The search pattern is now set to look for commas in the text.

The Regular Expression method Test checks if the text contains any matches. This is useful for 2 reasons. First is flow control. If there is pattern then deal with it and if not continue on. Second, is the Execute method will throw an error if there are no matches.

The Execute method will return a collection of all the matches found in the text along with any Submatches. I won't go into detail about these objects because they depend on how the expressions are written. The Matches index starts at zero. The object has a Count property to which tells us how many matches were found.

The Step function in a For loop is used to control the incrementing or decrementing of the control index value. The default is to increment by a value of +1. This can be changed to any value you want to use, positive or negative. The control index will start with the initial value and be increased or decreased by the Step value.

Hello LifeSeeker,

The variable Data is Variant that holds the values of the cells in the row. When you only need to work the value of cell and not any of the formatting, it is faster to copy the cells values into an Variant as a 2-D array (Rows and Columns). So the indices refer to the rows and columns in the array called Data.

You are correct about the Step function. It is counting down by one.

Hello Likeseeker,

The object variable Rng is first assigned to the contiguous cells on the worksheet starting with "A1". Since row 1 is header row, it can be excluded from the remaining range.

Lets say the range is A1:G10. Offsetting the range by 1 row down produces A2:G11. Now, row 11 has no data and needs to removed. To do that Intersect is used. This returns a Range that is the result of where 2 or more Ranges have cells in common. In this case, the Range result is A2:G10.

Hello Lifeseeker,

Here is the loop with comments added.

Please Login or Register  to view this content.

Hello Lifeseeker,

The variant array Data is filled with the cell values from a single row in the range (variable Rng). So, the element indices don't change. Data(1, 3) refers to the third column of the current row in the loop.

The total number of rows, the original plus the new rows, does equal the number of commas plus one. The line below calculates the number of new lines to be inserted.

Let's say [n]Rng[/b] is cells "A2:C10". The size of the array will be 9 by 3. If I want to know which row Rng(1, 3) refers to on the worksheet then some conversion is needed. Rng.Rows(1) returns the row index for Rng which is 1. Rng.Rows(1).Row returns the row index of the Parent of Rng which is the worksheet which is 2.

Hello Lifeseeker,

Let's say the Worksheet row (n) = 2 , and the comma count (c) is 2 (new lines are to be added).

Adding 2 rows above the current worksheet row 2 will push all the rows below row 2 down 2 rows. What was originally Row 2 is now row 4. To calculate the rows on the worksheet we use the formula:

Please Login or Register  to view this content.

The worksheet the range is on is the parent of that range.

Hello Lifeseeker,

Yes, you are correct. It should be 3, not 4. Two new lines will added at rows 2 and 3. The original row 2 has now been moved to row 4.

1) Meta-characters in regular expressions are

^　　$　　?　　*　　+　　.　　|　　{　　}　　\　　[　　]　　(　　)

each one of them has a special meaning.
And if you need to use those character as its original string in pattern, you will need to add an escape character that is back slash "\" in front of them.
e.g
\( \+ etc.
Obviously comma is not, so you don't need "\".

.Pattern ",(?!$)"
(?! means exclude the character that is in front of it, that followed by the character in the bracket.
$ means end of the line.
So it means, all comma excdept at the end.

So it only matches first 2 comma from "1,2,3,"

Does this help?

When it needs to add new row(s), it stores Col.A-B value of origin to variable temp.

And output it to Col.A-B of new rows added.

If you step through the code, you will find what it does.

While you are in VBE:

Click on somewhere on the code and hit F8.

As you hit F8, the code will execute line by line.
If you shrink the VBE window, you can see what is going on the sheet.

And more, if you open Local Window (goto View - LocalWindow).
You can also see all the variables in the LocalWindow.