- Please explain this Excel macro so I can adapt it for my own
- Excel macro auto increment number - How-To - Excel
- Excel macro active sheet ✓ - Forum - Excel
- Excel macro create new workbook and copy data ✓ - Forum - Excel
- Excel macro to merge cells based on condition ✓ - Forum - Excel
- Excel macro compare two sheets and highlight differences ✓ - Forum - Excel
Dim rng As Range, c As Range
Dim rng1 As Range, c1 As Range
Dim dest As Range, j As Integer, k As Integer
These steps clear memory for the data to be stored in. The variable types are Ranges, named rng, c, rng1, c1, dest
J is set up to be an integer (number), as is k.
Set rng = Range(.Range("A2"), .Range("A2").End(xlDown))
j = WorksheetFunction.CountA(.Rows("1:1"))
Basically, above is saying take the rng variable, and make it the Range of A2 all the way down. J is keeping track of the count. the important step here is the WITH statement, as this starts to create a group [or complete object] (not the technical terms for those reading and want to bust balls!)
The below steps, start looping with each FOR. PLease notice that you have two FORS looped together. This means that one for will have to go through every qualified item, before moving to the next item, and qualifying all of those, and so on. So you could end up with something looping 50 times (check for A) for 1(item X) item, then having to loop though 75 of item X's.
For Each c In rng
Set rng1 = Range(c.Offset(0, 1), .Cells(c.Row, Columns.Count).End(xlToLeft))
Notice the ' . these are REM statements, and those lines DO NOT get executed. You remove those REM marks for troubleshooting, if you wanted to see something get counted, for example!
For Each c1 In rng1
Set dest = Worksheets("sheet2").Cells(Rows.Count, "a").End(xlUp).Offset(1, 0)
If c1 = "" Then GoTo line1
'dest.Offset(0, 0) = c
'dest.Offset(0, 1) = .Cells(1, c1.Column)
'dest.Offset(0, 2) = c1
dest = c
dest.Offset(0, 1) = c1
dest.Offset(0, 2) = .Cells(1, c1.Column)
This is a whole loop. It changes the dest variable to c, and counts to the c1 value.
End of the second loop!
End of the GROUPING (from above)!
.NumberFormat = "dd-mmm-yy"
Of course the ending of the subroutine!
I hope this helps explain it so you can apply it to your application!