Please explain this Excel macro so I can adapt it for my own
Closed
muddakinjaz
Posts
1
Registration date
Tuesday August 9, 2016
Status
Member
Last seen
August 9, 2016
-
Aug 9, 2016 at 12:51 AM
Blocked Profile - Aug 9, 2016 at 04:18 PM
Blocked Profile - Aug 9, 2016 at 04:18 PM
Related:
- Please explain this Excel macro so I can adapt it for my own
- Excel apk for pc - Download - Spreadsheets
- Spell number in excel without macro - Guide
- Kernel for excel - Download - Backup and recovery
- Excel marksheet - Guide
- Macros in excel download free - Download - Spreadsheets
1 response
Sub test()
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.
Next:
Worksheets("sheet2").Cells.Clear
With Worksheets("sheet1")
Set rng = Range(.Range("A2"), .Range("A2").End(xlDown))
j = WorksheetFunction.CountA(.Rows("1:1"))
'msgbox j
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))
'msgbox rng1.Address
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)
'msgbox dest.Address
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)
line1:
Next c1
This is a whole loop. It changes the dest variable to c, and counts to the c1 value.
Next c
End of the second loop!
End With
End of the GROUPING (from above)!
With Worksheets("sheet2").Columns("c:c")
.NumberFormat = "dd-mmm-yy"
End With
Of course the ending of the subroutine!
End Sub
I hope this helps explain it so you can apply it to your application!
Have fun!
