Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array of collection of match objects from RegExp
I am trying to loop through a spreadsheet that has a number of lines
that are split up into groups. These groups all have the same value for column two, which is why they are grouped. These groups are all separated by a line that is empty except for a column that has a value in every line so that filtering works. I want to grab the cells in column one then cycle through them in a double loop and compare them with regex's to find the last in a chain that is defined like this: [0-9][0-9]L[1-4][A-Z][0-1] Now the first two numbers describe an area and the first number after L is the level number 1-4. The next letter is a sub area and the last number is a smaller sub area. So 63L1, 63L2A, 63L3A4 is a chain down. 63L3B4 is under 63L1 but not under 63L2A, it would be under 63L2B. 55L2A would be under 55L1 and so on. The lines are sorted first by column 2 then column 1 so there is a predictable order to the values in column 1. What I want to do is grab the first cell in each of these groups and put it into an array, GraphicsNames, then when I get a group of 2 or more I want to get elements of the text in that column into an array of regular expression collections to compare and find the last element of each chain. The larger the number after L the further down the chain. Using submatches I should be able to parse out the elements and make my determination. Can I make an array of collections to loop through? Here is what I have so far, I have everything working up to this point and need to know how to best handle all of the regexp collections. If you can suggest a better method than the one I have presented I would appreciate the suggestion. Sub AssocDisp() x = 2 Dim GraphicsCount As Integer Dim rgExp As New RegExp Dim Matches(15) As Object '****an attempt to make an array Dim GraphicNames(15) As String GraphicsCount = [A65536].End(xlUp).Row rgExp.IgnoreCase = True rgExp.Pattern = "([0-9][0-9])L([0-9]*)([A-Z])(.*)" Do While x < GraphicsCount 'number of graphic files y = 0 t = 0 u = 1 Do While Cells(x, 1).Value < "" GraphicNames(y) = Cells(x, 1).Value x = x + 1 y = y + 1 Loop If y 1 Then Do While t <= y Do While u <= y 'I get errors on teh next part, not sure what I need to do here. Set Matches(u) = rgExp.Execute(GraphicNames(u)) MsgBox Matches(u).Count MsgBox Matches(0)(u).SubMatches.Count ' Here is where I need to populate a regex array For Each booger In Matches(u)(0).SubMatches MsgBox booger 'Matches(0).SubMatches() Next u = u + 1 ' If sometest Then ' Do some stuff here ' ' End If Loop Loop ElseIf GraphicNames(0) < "" Then Cells(x - 1, 12).Value = "Considered" ' I want to put this text in (x,12) for the last element in chain End If x = x + 1 Loop End Sub Thanks! fybar |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array of collection of match objects from RegExp
fybar,
I don't think you need to use regular expressions. If you are looking to get the first cell of each of these "groups", you could either extract unique values, or you could look at the left X, X +1, and X+2 characters of each string compared to the next and/or previous string. What exactly you want to do is unclear since you did not post an exmple - if you want more help, post an example list and indicate which members should be selected.... HTH, Bernie MS Excel MVP "fybar" wrote in message ... I am trying to loop through a spreadsheet that has a number of lines that are split up into groups. These groups all have the same value for column two, which is why they are grouped. These groups are all separated by a line that is empty except for a column that has a value in every line so that filtering works. I want to grab the cells in column one then cycle through them in a double loop and compare them with regex's to find the last in a chain that is defined like this: [0-9][0-9]L[1-4][A-Z][0-1] Now the first two numbers describe an area and the first number after L is the level number 1-4. The next letter is a sub area and the last number is a smaller sub area. So 63L1, 63L2A, 63L3A4 is a chain down. 63L3B4 is under 63L1 but not under 63L2A, it would be under 63L2B. 55L2A would be under 55L1 and so on. The lines are sorted first by column 2 then column 1 so there is a predictable order to the values in column 1. What I want to do is grab the first cell in each of these groups and put it into an array, GraphicsNames, then when I get a group of 2 or more I want to get elements of the text in that column into an array of regular expression collections to compare and find the last element of each chain. The larger the number after L the further down the chain. Using submatches I should be able to parse out the elements and make my determination. Can I make an array of collections to loop through? Here is what I have so far, I have everything working up to this point and need to know how to best handle all of the regexp collections. If you can suggest a better method than the one I have presented I would appreciate the suggestion. Sub AssocDisp() x = 2 Dim GraphicsCount As Integer Dim rgExp As New RegExp Dim Matches(15) As Object '****an attempt to make an array Dim GraphicNames(15) As String GraphicsCount = [A65536].End(xlUp).Row rgExp.IgnoreCase = True rgExp.Pattern = "([0-9][0-9])L([0-9]*)([A-Z])(.*)" Do While x < GraphicsCount 'number of graphic files y = 0 t = 0 u = 1 Do While Cells(x, 1).Value < "" GraphicNames(y) = Cells(x, 1).Value x = x + 1 y = y + 1 Loop If y 1 Then Do While t <= y Do While u <= y 'I get errors on teh next part, not sure what I need to do here. Set Matches(u) = rgExp.Execute(GraphicNames(u)) MsgBox Matches(u).Count MsgBox Matches(0)(u).SubMatches.Count ' Here is where I need to populate a regex array For Each booger In Matches(u)(0).SubMatches MsgBox booger 'Matches(0).SubMatches() Next u = u + 1 ' If sometest Then ' Do some stuff here ' ' End If Loop Loop ElseIf GraphicNames(0) < "" Then Cells(x - 1, 12).Value = "Considered" ' I want to put this text in (x,12) for the last element in chain End If x = x + 1 Loop End Sub Thanks! fybar |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
series objects; finding specific ones in the collection | Charts and Charting in Excel | |||
VBA - array or collection literals? | Excel Programming | |||
Passing Objects from Excel VBA collection to a VB6 DLL | Excel Programming | |||
Range objects in a collection | Excel Programming | |||
Iterate over Collection Objects in Container. | Excel Programming |