Please explain this Excel macro so I can adapt it for my own

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

I have practically 0 knowledge about macros but I need to complete this task. I found this question that is similar to mine but I can't understand the answer given that I don't understand the meaning of the terms and steps for each line. I basically have to copy each row in 8761 rows (from B2:J2) and paste 4 times each in another sheet. I know this is a lot of work to answer my question, but I would greatly appreciate it. Please and thank you so much.


1 response

Blocked Profile
Aug 9, 2016 at 04:18 PM

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.


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)
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!