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?

Community · Accepted Answer · 2020-06-20 09:12:55Z

4

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

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 8, 2010 at 15:06

AMissico

21.8k7 gold badges80 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

js0823 Over a year ago

How do I check properties in VBA? Could you make it a little more clear? Sorry I am new to VBA scripting in Excel.

AMissico Over a year ago

"Check out" means read the help topics for those properties. Also, open the "Object Browser" and search for the properties.

Geoffrey · Accepted Answer · 2010-12-10 07:10:31Z

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.

answered Dec 10, 2010 at 7:10

Geoffrey

5,42910 gold badges49 silver badges81 bronze badges

Comments

drD · Accepted Answer · 2013-01-07 16:00:54Z

To add to Geoffrey's answer about active cell - it would also require that you activate the sheet you are looking to input your values if it is a different sheet from the one that is currently active. Additionally you would have to activate a cell to use activecell and the activecell offset property. For example

     'Activates the name of the sheet you would like to activate
    Sheets("Sheet2").Activate  

    'Activates cell A1
    Range("A1").Activate

    'Activates cell one row down, one column right
    ActiveCell.Offset(1,1).Select  

   'if current sheet is not activate you just do Sheets("Sheet2").Range("A1").Activate

The offset property of ActiveCell refers to other cells based off of the current active cell.

For example-

Offset(row,column) -

First Argument -Positive values as the first argument refer you to rows below the current active cell and Negative values refer you to rows above the current active cell

Second Argument-Positive values as the second argument refer you to columns right of the current active cell and Negative values refer you to columns left the current active cell

Collectives™ on Stack Overflow

How do I increment cell in Excel VBA script?

3 Answers 3

Range.Row Property

Example

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Range.Row Property

Example

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related