How to increment number using vb script

[Guest]

I have one excel spreed sheet on columnA one row A1 has "000002", on the 3rd
charactor I need to change to 2 for second row as "002002", then third row
will be as "004002", I need to increment 2 from the previous row until to the
end of row as"998002", how to run with the macro.
000002
002002
004002
006002
...........
998002

Thank you so much.

 

[Earl Kiosterud]

Lillian,

Dim a As Long
Dim MyCell As Range
Set MyCell = Range("C2") ' starting cell
a = 2000 ' starting value
Do While MyCell <> "" ' stop on empty cell
MyCell = MyCell + a ' add a
Set MyCell = MyCell.Offset(1, 0) ' move down
a = a + 2000 ' increment a
Loop

this presumes the original values are all 2. It doesn't replace, it adds.

 

[Dave Peterson]

Do you really need a macro:

Put something like this in A1 and copy down:

=TEXT((ROW()-1)*2,"000")&"002"

As a macro:

Option Explicit
Sub testme()
With ActiveSheet.Range("a1:a500")
.NumberFormat = "General"
.Formula = "=TEXT((ROW()-1)*2,""000"")&""002"""
.NumberFormat = "@"
.Value = .Value
End With
End Sub

 

[Guest]

Dave:

This work on both, thanks a lots. what is ROW()-1 means?


Lillian

 

[Dave Peterson]

If you put =row() in a worksheet cell, it returns the row of that cell.

If you put =row() in A37, you get 37 back.

=row()-1 is the row number - 1.

Since you wanted to start with 000 (in 000002), and you wanted to start in A1,
the formula had to subtract 1 to get to 0.