How to learn VBA Code by breaking it down

[iheartvba]

Hi I have got the following code from someone else

Sub MultiColsToA() 
    Dim rCell As  Range 
    Dim lRows As Long 
    Dim lCols As Long 
    Dim lCol As Long 
    Dim ws As Worksheet 
    Dim wsNew As Worksheet 
     
    lCols = Columns.Count 
    lRows = Rows.Count 
    Set wsNew = Sheets.Add() 
     
    For Each ws In Worksheets 
        With ws 
            For Each rCell In .Range("B1", .Cells(1, lCols).End(xlToLeft)) 
                .Range(rCell, .Cells(lRows, rCell.Column).End(xlUp)).Cut _ 
                wsNew.Cells(lRows, 1).End(xlUp)(2, 1) 
            Next rCell 
        End With 
    Next ws 
     
End Sub

This basically takes all columns from all worksheets in excel and pastes it into 1 column.

the problem is I don't understand the code, I want to know how I search google to learn what this code is doing

so I understand until line 11 but then I get lost

I am assuming Lines 13 and 20 say do the following code in each worksheet

then line 14 says in the current worksheet do the code begining in line 15

Line 15 Queries:
Query 1: "For Each rCell": Where is rCell given a value?
Query 2: For the rest of the code I assume its saying make the range from B1 to whatever the last column number is.
Line 16 Queries:
Query 1: I have no idea whats going on here
Line 17 Queries:
Query 1: I have no idea whats going on here


My objective is similar to this code but I want excel to take each consecutive set of 3 columns and paste it into the first 3 columns e.g.: take Column D,E and F and paste them underneath A,B and C. Then take Columns G, H and I also page them underneath A,B and C, and on it goes until all columns are pasted under Columns A, B and C.

Thank You

[kadghar]

if you debug it line by line (by pressing F8 instead of F5), each time you press F8 you'll find out what each line does.

about your questions:
lines 1 to 7 defines the variables
9 and 10 count the columns and rows
and then it make FORs for every worksheet, and every range in each worksheet.

The copy- paste code is in lines 16 and 17.

Well, what you have to do is quite simple, instead of making a FOR EACH, try using a FOR with STEP 3, and use it 3 times.

Oh, and check this out. It's my experience of how using variables is quite more efficient than using Copy-Paste in Excel's VBA. So why dont you give it a try this other way?

[iheartvba]

Could I get the code you think would acheive the objective.

Ok, thanks I understand it a bit better but still not completely. Could you post the code that you think would work for what I want to acheive. Sorry I am feeling a bit thick in the brain at the moment.


Thank You

[kadghar]

well, writing it for you wont solve your doubt, because you wont understand it... but please, at least check the FOR, DO, REDIM, RANGE and CELLS syntax, and check the article i recomended you before, so you can see why im writing it all into variants.

i came with something like this...
[CODE=vb]Sub ColsMerge()
Dim i As Integer, j As Integer, n As Integer
Dim x As Double, y As Double, z As Double
Dim a, b()

y = Rows.Count: x = 1

n = Int(InputBox("I n how many columns will the sheet be merged?", "Merge Columns", "3"))
For i = 1 To Columns.Count
If Cells(1, i) = "" Then Exit For
z = Cells(1, i).End(-4121).Row
If z > x And z < y Then x = z
Next
a = Range(Cells(1, 1), Cells(x, i - 1))
ReDim b(1 To x * i / n, 1 To n)
For j = 1 To n
z = 1
For i = j To UBound(a, 2) Step n
y = 1
Do
If a(y, i) <> "" Then
b(z, j) = a(y, i)
z = z + 1
End If
y = y + 1
If z > Rows.Count Then Exit For
Loop Until y > x
Next
Next
Range(Cells(1, 1), Cells(UBound(b) , n)) = b
End Sub[/CODE]

that is not quite error prove... and it has some limitations, like, it wont copy formulas, only values, and any empty colum will stop the columns searching. and it will skip empty cells... but that can be changed in the if in the middle of the DO/LOOP

HTH

[iheartvba]

Thanks kadghar Post # 4 helped me understand much better as I could actually see the code doing what I wanted

[divy05]

Good Post!

Good Post! Learning things by breaking up the task into smaller sub tasks makes it more clear to understand. There are tutorials available for coding languages but for VBA, a video tut is not available as open source. Any ideas of such videos folks, if yes then please do share the info. Thank You!

[Killer42]

To be honest, I think you'll get better results by hitting Google to search for VBA tutorials.

You can also try asking in the Access/VBA forum, where our VBA experts hang out.