Overlapping Inner and Outter Loops

jerbaldw

Board Regular
Joined
Sep 6, 2012
Messages
89
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.

Code:
[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:

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


Using a more edit friendly loop structure like:

Code:
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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Can you post some sample data and expected results?
 

jerbaldw

Board Regular
Joined
Sep 6, 2012
Messages
89
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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,
Code:
       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.
Code:
       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.
Code:
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

Board Regular
Joined
Sep 6, 2012
Messages
89

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 6, 2012
Messages
89
Thanks for the help. To help others, this is what Norie's solution provided me:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,837
Messages
5,598,378
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top