Overlapping Inner and Outter Loops

[jerbaldw]

Trying to find a more friendly way to loop through a large string
and fill specific substring values to each cell in a range. Currently I have created
these brute force types of codes, but it becomes a pain when a update needs to be made. Thanks for the help.

Couple different examples of what I'm using.

[COLOR=#008000]   '
[/COLOR]   [COLOR=#008000]'NG REASONS:
[/COLOR]       For q = 5 To 16
            Sheets("ATT").Range("B" & q) = Mid(Sheets("ATT").[B20], q - 4, 1)
        Next q
    [COLOR=#008000]'
    'RCVD BY:
[/COLOR]       For q = 15 To 26
            Sheets("ATT").Range("C" & q - 10) = Mid(Sheets("ATT").[B20], q, 1)
        Next q
[COLOR=#008000]   '
    'DATE RCVD:
[/COLOR]       For q = 29 To 95 Step 6
            Set Dval = Sheets("ATT").Range("D" & ((q - 5) / 6) + 1)
            Dvals = Mid(Sheets("ATT").[B20], q, 6)
            Dval = Format(Dvals, "##/##/##")
        Next q

I would like to loop the outer loop once, then the innner loop once -- then start again with the outter loop.
So something like this for example where the third loop would be:

Sheets("ATT").Range("C7") = Mid(Sheets("ATT").[B20], 17, 1)

Using a more edit friendly loop structure like:

For q = 15 to 26
    For i = 5 to 16
        Sheets("ATT").Range("C" & i) = Mid(Sheets("ATT").[B20], q, 1)
    next i
next q

 

[Norie]

Can you post some sample data and expected results?

 

[jerbaldw]

Im using a string that captures entries from a template encyptes them into alpha numeric type strings so that another user
can input the encrypted string into a "decrypt" type function that will auto populate the template
to match the original (being uploaded into another system). Everything works fine, but its a PAIN to update this portion of the code.
I know from my code that specific substrings values of the main string represents
specific values to be repopulated back into a table of values. Then I run
another function that takes the values and runs them to determine what should
be entered back into the template. (values to be selected are controlled)

This part of the code takes the encrypted string and removes all the values into a rows (eventually another program loops these rows
to repopulate original values). Thanks for the help, this will save me much time.


So for this example. I have a enrcypted string of:
ABDXXXXXXXXX 725XXXXXXXXX 010112020212030312XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XX:XX:XXXX:XX:XXXX:XX:XXXX:XX:XXXX:XX:XXXX:XX:XXXX:XX:XXXX:XX:XXXX:XX:XXXX:XX:XXXX:XX:XXXX:XX:XX
X X X (@)07jamie b 071885 (*)19aa11bb22cc33dd44ee5 XXXXX.XX.XX.XX.X 041813 XXXXXX

The First Loop would return into cells (B5:B16)
A
B
D
X
X
X
X
X
X
X
X
X
Second Loop Would return into cells (C5:C16)
7
2
5
X
X
X
X
X
X
X
X
X
Third Loop would return into cells (D5:D16)
01/01/12
02/02/12
03/03/12
XXXXXX
XXXXXX
XXXXXX
XXXXXX
XXXXXX
XXXXXX
XXXXXX
XXXXXX
XXXXXX

 

[Norie]

Are there actual spaces between each 'section' of the string?

If there is you could start by splitting the string using Split.

You could then loop through each part of it and take appropriate action.

It might be an idea to create a couple of functions/subs to do some of the work, for example a sub that will split out each character of a string passed to it.

By the way, couldn't you write this,

       For q = 5 To 16
            Sheets("ATT").Range("B" & q) = Mid(Sheets("ATT").[B20], q - 4, 1)
       Next q

       For q = 15 To 26
            Sheets("ATT").Range("C" & q - 10) = Mid(Sheets("ATT").[B20], q, 1)
       Next q

as this.

       For q = 5 To 16
            Sheets("ATT").Range("B" & q) = Mid(Sheets("ATT").[B20], q - 4, 1)
     
            Sheets("ATT").Range("C" & q) = Mid(Sheets("ATT").[B20], q+10, 1)
        Next q

PS You can use split to get an array of each character in a string without looping.

Dim arr As Variant
Dim strTest As String

   strTest = "ABDXXXXXXXXX"

   arr = Split(StrConv(strTest, vbUnicode), Chr(0))

   ReDim preserve arr(UBound(arr)-1)

   Range("B5:B16").Value = Application.Transpose(arr)

 

[jerbaldw]

Yes, I use spaces to help me manage it. This was exactly what I needed (another mind). I'm unfamiliar with the split syntax, I just tried it out and it worked great for the first loop! I'm having trouble trying to put it together for all the string lengths.. Any ieas? Thanks for the last post, this is an amazing funciton for VBA I had no idea existed.

 

[Norie]

Not quite sure what you mean by 'string lengths'.

You won't be able to use split to separate by a specific no of characters if that's what you mean, it only works to split out each character.

 

[jerbaldw]

Thanks for the help. To help others, this is what Norie's solution provided me:

Sub SPLIT()
'
Dim arr As Variant
Dim strTest As String
'
strTest = Mid([A2], 15, 12)
'
arr = SPLIT(StrConv(strTest, vbUnicode), Chr(0))
'
ReDim Preserve arr(UBound(arr) - 1)
'
Range("B5:B16").Value = Application.Transpose(arr)
'
End Sub