Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a conditional formula to increment #s in a test script | Excel Discussion (Misc queries) | |||
Auto Increment Number | Excel Discussion (Misc queries) | |||
increment version number | Excel Discussion (Misc queries) | |||
How do I Increment a particular woksheet number? | Excel Worksheet Functions | |||
Increment number by 1 | Excel Programming |