Can someone explain this code to me please

[Jack-P-Winner]

I would like to know how to read this code so I can make some changes.

What is t?
What is x?
I assume r is row? but how is it used in this Macro
What does Dim x

Thanks for your help, my goal is I want to change the row it starts on and also place the digits in 3 columns instead of spreading them over 20+ columns. I have 48 digits that get spread out across 38 columns and I am trying to get them to be in groups of 3 digits so like they would go in BCD and go down as many rows as needed. I guess in this case 16 rows 16x3=48

Sub SplitData()


Dim x As Long, t As Long
 For Each r In Range("A:A").SpecialCells(xlCellTypeConstants)
 t = 1
 For x = 1 To Len(r.Value)
 If Mid(r.Value, x, 1) Like "[0-9]" Then
 t = t + 1
 Cells(r.Row, t).Value = Mid(r.Value, x, 1)
 End If
 Next
 Next
 End Sub


 [CODE]

 

[Luke M]

First, the explanation

Sub SplitData()
'In good code writing, we define all our variable, and let VB know
'what type of variable they are. Different types like Strings, Range, Integer, etc.
'You can write each definition as a separate line like:
'Dim x As Long
'Dim y As Long
'
'or on one line:
'Dim x As Long, t As Long
'Both are equivalent

'In this case, 2 variables are defined. The Long (long integer) is a non-decimal number ranging from
' -2,147,483,648 to 2,147,483,647
'For more help, highlight the word in VBE, and hit F1
Dim x As Long, t As Long

'This is actually a bad writing..the variable r was never defined, so VB will figure it out
'on the fly. In this context, it will be defined as a range, or specifically, a single cell.
'Think of this line as saying "For each in the range of cells in col A that have a constant value..."

For Each r In Range("A:A").SpecialCells(xlCellTypeConstants)
    'Every time we go through this loop, reset t = 1
    'Looks like this is a column counter
    t = 1
   
    'The Len() method returns the length of a string. E.g., Len("Cat") = 3
    'This line says to start a loop, where x will start at 1, and eventually reach the length of text in the r cell
    For x = 1 To Len(r.Value)
        'Code is now going through an checking each character in r cell
        'Using the Mid function. This works by looking at 1st character, then 2nd, 3rd, etc.
        'to see if it's the number 0-9
        If Mid(r.Value, x, 1) Like "[0-9]" Then
            'If it is a number, increment our column counter...
            t = t + 1
            '...and write that number into a cell next to the r Cell
            Cells(r.Row, t).Value = Mid(r.Value, x, 1)
            'We see now that the column counter is incremented so that if we have multiple
            'results, they each get written into their own column
        End If
    Next
Next
End Sub

I'm not sure it would be easier to adapt this to what you want...the bit about looking at Constant cells only and looking at each character seems different from what you mention. Could you post a short example showing what you have currently, and what you want it to look like when done?

 

[Jack-P-Winner]

I will try my best as I feel the final project may have to be done in steps or with a couple different Macros but as you can tell I am not a whiz with them so I am really not sure. I a in baby step mode. I have uploaded a couple of pictures and a file. I run one macro to split all the data in to its own cell. When I get the numbers in this case all 144 are in ONE cell so I put them in their own. I labeled the pics as " I want to go from here" the start to End "the finished results. I also have a 53 second video so you can see what I am presently doing. Thanks Luke!!

https://onedrive.live.com/redir?resid=ABA6DC5F6B4EABEB!1883&authkey=!AM1AI1JtptrczTg&ithint=video,mp4

 

[Luke M]

I don't understand your spacing, nor know why it might be important. As always, I would suggest leaving data in the simplest layout, and then using formulas to adjust position/format if needed. Anyway, here's a start that will read a range, group into X sized numbers, eliminate duplicates, and then spit it back out.

Sub BuildStrings()
Dim AllNums As Variant
Dim myDic As Object
'Use a dictionary, which won't allow duplicates
Set myDic = CreateObject("scripting.dictionary")

Dim tempWord As String
Dim i As Long, j As Long

'What size group?
Const groupLen As Integer = 3

'May need to make this dynamic
AllNums = Worksheets("Combos").Range("B2:DQ4").Value

'Loop through all numbers, combining them into groups
For i = 1 To UBound(AllNums, 1)
    For j = 1 To UBound(AllNums, 2)
        'Build a temporary group
        tempWord = tempWord & AllNums(i, j)
        'If it's long enough, then add to dictionary
        If Len(tempWord) = groupLen Then
            On Error Resume Next
            myDic.Add tempWord, "x"
            On Error GoTo 0
            'Reset our variable
            tempWord = ""
        End If
    Next j
Next i

MsgBox "Unique entries found: " & myDic.Count

'===========
'FIRST PART COMPLETE
'Begin output back to a sheet
'===========

Dim myKeys As Variant
Dim xRow As Long, xCol As Long
Dim newArray As Variant
Dim ws As Worksheet

'How wide can we go? Should be a multiple of groupLen
Const colLimit As Long = 12

'Array to hold our new values
Set ws = ThisWorkbook.Worksheets.Add
newArray = ws.Range("A1", ws.Cells(1000, colLimit)).Value

xRow = 1
xCol = 1


'Split text back up to single digits
myKeys = myDic.keys
For i = 0 To UBound(myKeys)
    For j = 1 To groupLen
        newArray(xRow, xCol) = CLng(Mid(myKeys(i), j, 1))
        xCol = xCol + 1
        If xCol > colLimit Then
            xCol = 1
            xRow = xRow + 1
        End If
    Next j
Next i

'Write our results
ws.Range("A1", ws.Cells(1000, colLimit)) = newArray

'Clear the memory
Set myDic = Nothing

End Sub

 

[Jack-P-Winner]

On the spacing, if you mean why I have it laid out the way I do in a square of 9 numbers? I do not mind explaining if you tell which spacing Luke Thanks Ill try this out

 

[Luke M]

That was it. The row and column spacing. Either way, I think it's simpler to do this first to get the numbers, and then have another macro do the formatting/adjusting by inserting rows/columns as needed.

 

[Jack-P-Winner]

Luke,

Its giving me an error saying I have O entries. The numbers are in cell block B:2

 

[Jack-P-Winner]

darn it I know why Luke, I left out a picture. I explained it but did not show it. My bad. The way the pic is above is how I paste them into excel all in one cell before they are broken out or at least I think that's why it may bot be working??

 

[Jack-P-Winner]

When I run the Macro it puts the numbers in various column's and not in 3x3 squares. Spacing is critical

 

[Jack-P-Winner]

Luke,

I wrote you a detailed conversation only to find out I can not email you anymore LOL. I was trying to explain to you what I was doing and why.hmmm, anyhow, this is the final outlook of these squares that I am talking about after I color code them and why spacing is important to me

 

[Luke M]

Since data is all in one cell, change code as marked.

Sub BuildStrings()
Dim AllNums As Variant
Dim myDic As Object
'Use a dictionary, which won't allow duplicates
Set myDic = CreateObject("scripting.dictionary")

Dim tempWord As String
Dim i As Long, j As Long

'What size group?
Const groupLen As Integer = 3


'==BEGIN CHANGE==
'May need to make this dynamic
AllNums = Worksheets("Combos").Range("B2").Value

'Loop through all numbers, combining them into groups
For i = 1 To Len(AllNums)
   
        'Build a temporary group
       tempWord = tempWord & Mid(AllNums, i, 1)
        'If it's long enough, then add to dictionary
       If Len(tempWord) = groupLen Then
            On Error Resume Next
            myDic.Add tempWord, "x"
            On Error GoTo 0
            'Reset our variable
           tempWord = ""
        End If
   
Next i
'==END CHANGE====

MsgBox "Unique entries found: " & myDic.Count

'===========
'FIRST PART COMPLETE
'Begin output back to a sheet
'===========

Dim myKeys As Variant
Dim xRow As Long, xCol As Long
Dim newArray As Variant
Dim ws As Worksheet

'How wide can we go? Should be a multiple of groupLen
Const colLimit As Long = 12

'Array to hold our new values
Set ws = ThisWorkbook.Worksheets.Add
newArray = ws.Range("A1", ws.Cells(1000, colLimit)).Value

xRow = 1
xCol = 1


'Split text back up to single digits
myKeys = myDic.keys
For i = 0 To UBound(myKeys)
    For j = 1 To groupLen
        newArray(xRow, xCol) = CLng(Mid(myKeys(i), j, 1))
        xCol = xCol + 1
        If xCol > colLimit Then
            xCol = 1
            xRow = xRow + 1
        End If
    Next j
Next i

'Write our results
ws.Range("A1", ws.Cells(1000, colLimit)) = newArray

'Clear the memory
Set myDic = Nothing

End Sub

As for spacing, what I said before holds true. Rather than trying to take your raw data, split it, and move it all together, it's better to leave your raw data in one place, and then use formulas to arrange it how you want (which seems to change from time to time). For instance, see attached. I have the raw data like I did before (still has old code based on previous example you gave), but you can see on Output sheet how I easily got the numbers aligned how you might want, PLUS it's flexible to change/update.

 

[Jack-P-Winner]

I am getting the couple errors and are you telling me that the spreadsheet will wind up looking like the one I uploaded here named "Runs like this" and has data across A:L. If so, that will definitely not work as it will scramble all the data and it has to stay intact and in order like in the video

 

[Luke M]

I'm telling you to not worry about making the macro organize the numbers. You want to have a flow of items:
1. Raw input (which I think is a single cell now..??)
2. Output of numbers, with no duplicate 3 digits
3. Formatted/arranged as you desire

These should be in separate locations, not over-writing each other. This way, your process can repeat itself if necessary, and you can audit what went wrong, if something does. That was the point of showing you the INDEX function. You will want step 2 to be a solid block of numbers. Then, to get your 3x3, or 4x3, or whatever you fancy size grids, use formulas.

As for the macro, it runs on my machine. Perhaps you can send me a new example, since the original has changed since your last XL file.

PS. I can't view the video. I can only teach ideas/tools on how to use XL, and hope that others can learn from them and adapt to their needs.

 

[Jack-P-Winner]

The video was to shoe you how I have to have it set up, nothing more

 

[Jack-P-Winner]

How can I go from everything in columns and place them in squares? For some reason I could not get it to run on a new sheet so I used the one I posted and it works great (-: THANK YOU!!!!

Thanks

 

[Luke M]

Go back to the file I posted above:
http://chandoo.org/forum/threads/can-someone-explain-this-code-to-me-please.23031/#post-139082

On the output sheet, cells in red are your inputs, cells in gray are output via formulas. This is how you go from a block of data to having them arranged in squares, rectangles, etc. In my file, try changing the any of the orange cells to see how the output changes.

TL;DR: Use INDEX function

 

[Jack-P-Winner]

When I place the macro in a new sheet and the data in cell block B:2 I get this error. I know you said it runs fine for you but not for me and no clue why

 

[NARAYANK991]

Hi Larry ,

The problem is because of the decimal period symbol. See if this works.

Narayan

 

[Jack-P-Winner]

Hi Larry ,

The problem is because of the decimal period symbol. See if this works.

Narayan

PERFECT!!! THANK YOU!!!!

Now how do I get it into cubes of 3x3, 3x4 and 4x4

3 x 3 is 3columns of numbers by 3 rows of numbers
3 x 4 is 4 columns of numbers by 3 rows of numbers
4 x 4 is 4 columns of numbers by 4 rows of numbers

 

[Luke M]

Again, as I said before, use the INDEX formula. I knew you might have different layouts, which is why I gave you a solution that can handle different scenarios.

Just change the input squares (orange cells in original example), and the INDEX will grab the correct values.

 

[Jack-P-Winner]

Again, as I said before, use the INDEX formula. I knew you might have different layouts, which is why I gave you a solution that can handle different scenarios.

Just change the input squares (orange cells in original example), and the INDEX will grab the correct values.

Luke,

Here is my confusion, all I see in orange is

Sub BuildStrings()
Dim AllNums As Variant
Dim myDic As Object
'Use a dictionary, which won't allow duplicates
Set myDic = CreateObject("scripting.dictionary")

Please remember, although I may post some complex things, I am new to Macros and uncertain what or how to change what you are calling the INDEX. Perhaps if you can show me ONE way to change it in any of the formats I am asking (3x3, 3x4,4x4) then I will get it and can make the other changes and later down the road. Thanks Luke for your understanding and patience (-:

 

[Luke M]

The orange cells on the worksheet. The code just moves things into the initial data sheet.
3x3 blocks


3x4 grids

 

[Jack-P-Winner]

The orange cells on the worksheet. The code just moves things into the initial data sheet.
3x3 blocks
View attachment 18415

3x4 grids
View attachment 18416

Now I finally get it, I went back and looked. I did NOT download that file and was using my original. So sorry and thank you VERY MUCH!!! I get it now