How to increment number using vb script
 

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.

How to increment number using vb script
 

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.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Lillian" wrote in message
...
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.

How to increment number using vb script
 

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

Lillian wrote:

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.


--

Dave Peterson

How to increment number using vb script
 

Dave:

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


Lillian

"Dave Peterson" wrote:

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

Lillian wrote:

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.


--

Dave Peterson

How to increment number using vb script
 

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.

Lillian wrote:

Dave:

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

Lillian

"Dave Peterson" wrote:

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

Lillian wrote:

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.


--

Dave Peterson


--

Dave Peterson