Cell.Offset Comprehension

[lynyrdskynyrd5]

Sub DupeRows()
Dim cell As Range
'First cell with number of tickets
    Set cell = Range("B2")
        Do While Not IsEmpty(cell)
            If cell > 1 Then
                Range(cell.Offset(1, 0), cell.Offset(cell.Value - 1, 0)).EntireRow.Insert
                Range(cell, cell.Offset(cell.Value - 1, 1)).EntireRow.FillDown
            End If
            Set cell = cell.Offset(cell.Value, 0)
        Loop
End Sub

Could anyone please put a comment above each code line with cell.offset argument so I could better understand how a particular value came up using cell.offset

I am now on page 334-Excel 2016 Power Programming with VBA (Mr. Spreadsheet's Bookshelf) - Michael Alexander. I cannot just move further on the next page of the book as I am sure I will encounter the same cell.offset argument ahead.

 

[footoo]

https://msdn.microsoft.com/en-us/library/office/ff840060(v=office.15).aspx

 

[lynyrdskynyrd5]

I could not find in the above link a code line like this

Range(cell.Offset(1,0), cell.Offset(cell.Value-1,0))

is cell.Offset(1,0)="B3" in reference to "B2" which is the initial cell value?
what is cell.Offset(cell.Value-1,0) in the above code line equal to?
Does the above code line mean Range("B3", "?")?

 

[footoo]

is cell.Offset(1,0)="B3" in reference to "B2"

Yes

what is cell.Offset(cell.Value-1,0) in the above code line equal to?

It offsets B2 by the the number of rows equal to : the value in B2 minus 1

 

[gallen]

That's a pretty confusing routine! Not sure what it's trying to achieve but I reckon it makes more sense if you are reading the book.

 

[lynyrdskynyrd5]

So Range(cell.Offset(1,0), cell.Offset(cell.Value-1,0)) = Range(3,1) that is if the initial value of b2=2?

 

[RoryA]

If cell is B2 and B2 contains 3, then that equates to:

Range(Range("B2").Offset(1,0), Range("B2").Offset(3-1,0))

which is:

Range("B3", Range("B2").Offset(2,0))

which is:

Range("B3", "B4")

The syntax is always Range.Offset(number_of_rows_to_offset, number_of_columns_to_offset)

 

[gallen]

Have you tried stepping through each line of code seeing what it does?

If our variable 'cell' is referencing "B2" and the value of "B2" is 4 then break it down: (Ive used 4 as it makes it clearer. a value of 2 only enters 1 row)

Remember that a range object can take 2 cell reference arguments which is what is happening here. The first cell is in red the 2nd in blue

Range(cell.Offset(1,0),cell.Offset(cell.Value-1,0))

cell.Offset(1,0) = B2 offset by 1 row and 0 columns which equates to B3

cell.Offset(cell.Value-1,0) is slightly more complicated as the number of rows we are offsetting is the value of B2 (which is 4) minus 1 so cell.Offset(cell.Value-1,0) can also be written as cell.Offset(4-1,0) which equates to cell.Offset(3,0). So that line is referencing B5.

Taking that into account

Range(cell.Offset(1,0), cell.Offset(cell.Value-1,0)) is the same as

Range(Range("B3"),Range("B5")) which is the same as

Range("B3:B5")

So the code

[COLOR=#574123] Range(cell.Offset(1, 0), cell.Offset(cell.Value - 1, 0)).EntireRow.Insert[/COLOR]

inserts 3 blank rows below B2 at B3:B5. The 'filldown' line just copies the value of cell into the blank rows

Still confusing but it's important to breakdown each part of the line. I'd get familiar with using your 'Immediate' window while stepping through code to get values and understand what is going on clearer

 

[ukmikeb]

Hi

I've annotated the code :-

Sub DupeRows()
Dim cell As Range
'First cell with number of tickets
    Set cell = Range("B2")
        Do While Not IsEmpty(cell)
'              If the cell with number of tickets is empty - stop looping
            If cell > 1 Then
                c$ = Range(cell.Offset(1, 0), cell.Offset(cell.Value - 1, 0)).Address
' Insert one or more rows below the current row dependent on the number of tickets in the current cell
                Range(cell.Offset(1, 0), cell.Offset(cell.Value - 1, 0)).EntireRow.Insert
                c$ = Range(cell.Offset(1, 0), cell.Offset(cell.Value - 1, 1)).Address
' Copy the contents of the entire row (current row) to the inserted rows ie duplicate the row contents
                Range(cell, cell.Offset(cell.Value - 1, 1)).EntireRow.FillDown
            End If
                c$ = cell.Offset(cell.Value, 0).Address
' Move the cell to point to the cell after the inserted row/s
            Set cell = cell.Offset(cell.Value, 0)
        Loop
End Sub

and added the lines :-

                c$ = Range(cell.Offset(1, 0), cell.Offset(cell.Value - 1, 0)).Address
                c$ = Range(cell.Offset(1, 0), cell.Offset(cell.Value - 1, 1)).Address
                c$ = cell.Offset(cell.Value, 0).Address

so that if you put a breakpoint on each of the lines following you can see what the value of c$ (the cell address) is as you step through the code.

hth

 

[lynyrdskynyrd5]

The goal is to duplicate rows based on the value in column B. The macro duplicates the rows so that each person will have a row for each ticket purchased. For example, Barbara purchased two tickets,so she should have two rows (and two chances to win).
Name NumberOfTickets Random
Allan 1 647604
Barbara 2 764406
Charlie 1 465205
Dave 5 565005

Desired Outcome
Name NumberOfTickets Random
Allan 1 764646
Barbara 2 744606
Barbara 2 423406
Charlie 1 465647
Dave 5 646476
Dave 5 568500
Dave 5 467460
Dave 5 565647
Dave 5 646605