F
fybar
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
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