How do I increment cell in Excel VBA script?

Question

I have a data in excel which I want to make a VBA script to copy it into a new worksheet but in a different way. For example, I have this in sheet1 in A1~A3 cells.

Adam(A1)
Sam(A2)
Smith(A3)

I want to use these cells and create the following in another worksheet using refedit control.

Adam(A1)
Adam(A2)
Adam(A3)
Adam(A4)
Sam(A5)
Sam(A6)
Sam(A7)
Sam(A8)
Smith(A9)
Smith(A10)
Smith(A11)
Smith(A12)

I have refedit control in place in VBA script, but I'm not sure how to increment cell numbers to make it copy and paste into a new worksheet. I would like to use refedit control so that I can assign any cells and make it copy and repeat itself. How do I do this in VBA script?

Answer 1

Check out the Range Rows, Cells, and Address properties. This should help. Your question is too vague for a direct answer.

---

(This will get you started.)

Range.Row Property

http://msdn.microsoft.com/en-us/library/bb221550(office.12).aspx

Returns the number of the first row of the first area in the range. Read-only Long.

Example

For Each rw In Worksheets("Sheet1").Rows
    If rw.Row Mod 2 = 0 Then
        rw.RowHeight = 4
    End If
Next rw 

Answer 2

To increment cells in Excel VBA, you can use the Offset-property of the Range-object, e.g.

ActiveCell.Offset(1, 1).Select

will select the cell one row down and one column to the right of the active cell.