Is there any way to condense this into a single loop, as opposed to doing several iterations?-CodePudding

and thanks in advance for the help.

Essentially what I'm trying to do is take every 66th character from a string in Sheet1.Cells(2, 3) starting at the first character (total would be 20 times, the string length is 1,320 characters in all). Once I get those 20 occurrences of the 66th character, I would like to print those 20 instances on Sheet2.Cells(i, 2) where i = 2 to 21. Finally, I would like to loop to the next string at Sheet1.Cells(3, 3), do the same calculation, and print the results in Sheet2.Cells(i 20, 2) where still i = 2 to 21.

Below is the code that I have, and it does work. Problem is I would have to reiterate those blocks of code over a thousand times. Is there any way to condense this so it does this calculation to say every row until it hits Sheet1.Cells(2, 100)? Link attached of screenshot describing what I'm trying to accomplish. https://imgur.com/a/aBrlAzd

Sub Decode_String()

Dim Fault_Block(2000)

For i = 2 To 21
    For j = 1 To 20
        Fault_Block(i) = Mid(Sheet1.Cells(2, 3), (66 * j) - 65, 4)
        Sheet2.Cells(j   1, 2) = Fault_Block(i)
    Next
Next

For i = 22 To 41
    For j = 1 To 20
        Fault_Block(i) = Mid(Sheet1.Cells(3, 3), (66 * j) - 65, 4)
        Sheet2.Cells(j   21, 2) = Fault_Block(i)
    Next
Next

For i = 42 To 61
    For j = 1 To 20
        Fault_Block(i) = Mid(Sheet1.Cells(4, 3), (66 * j) - 65, 4)
        Sheet2.Cells(j   41, 2) = Fault_Block(i)
    Next
Next

End Sub

CodePudding user response：

You're on the right track, you just need to add a loop around your logic to move through the Sheet1 cells

Sub Decode_String()

Dim Fault_Block(2000)
Dim counter

' loop through cells
For c = 2 To 100
    For j = 1 To 20
        counter = counter   1
        Fault_Block(counter) = Mid(Sheet1.Cells(c, 3), (66 * j) - 65, 4)
        Sheet2.Cells(j   ((c - 2) * 20)   1, 2) = Fault_Block(counter)
    Next
Next

End Sub

If you don't need the Fault_Block array for another reason, you can simplify it to:

Sub Decode_String()

' loop through cells
For c = 2 To 100
    For j = 1 To 20
        Sheet2.Cells(j   ((c - 2) * 20)   1, 2) = Mid(Sheet1.Cells(c, 3), (66 * j) - 65, 4)
    Next
Next

End Sub

CodePudding user response：

Here are the loops condensed:

Sub Decode_String()
      For i = 2 To 21
          For j = 1 To 20
              Sheet2.Cells(j   1, 2) = Mid(Sheet1.Cells(2, 3), (66 * j) - 65, 4)
              Sheet2.Cells(j   21, 2) = Mid(Sheet1.Cells(3, 3), (66 * j) - 65, 4)
              Sheet2.Cells(j   41, 2) = Mid(Sheet1.Cells(4, 3), (66 * j) - 65, 4)
          Next
     Next
End Sub

If you wanted it to loop all the way to row 100, try this:

Sub Decode_String()
      For i = 2 To 21
          For j = 1 To 100
              Sheet2.Cells(j   1, 2) = Mid(Sheet1.Cells(2, 3), (66 * j) - 65, 4)
              Sheet2.Cells(j   21, 2) = Mid(Sheet1.Cells(3, 3), (66 * j) - 65, 4)
              Sheet2.Cells(j   41, 2) = Mid(Sheet1.Cells(4, 3), (66 * j) - 65, 4)
          Next
     Next
End Sub