Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number Cells As Cell Value Changes, Excel 2000 & 2003
Hello,
My worksheet is setup with 3 columns multiple rows varying in length. Example: Col-1 Col-2 Col-3 AIR COMP AIR COMPRESSOR CONVEYORS CONVEYORS DKFTN DRINKING FOUNTAIN DKFTN DRINKING FOUNTAIN DOM TK-EL HOT WATER TANK-ELEC EMER LIGHT EMERGENCY LIGHT EMER LIGHT EMERGENCY LIGHT EMER LIGHT EMERGENCY LIGHT I would like number Col-1 in this format: Col-1 Col-2 Col-3 001 AIR COMP AIR COMPRESSOR 001 CONVEYORS CONVEYORS 001 DKFTN DRINKING FOUNTAIN 002 DKFTN DRINKING FOUNTAIN 001 DOM TK-EL HOT WATER TANK-ELEC 001 EMER LIGHT EMERGENCY LIGHT 002 EMER LIGHT EMERGENCY LIGHT 003 EMER LIGHT EMERGENCY LIGHT Some of my worksheets have a lot of data and i would like to use a macro to insert the number for each item as example describes above. Thank you for your help, jfcby |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number Cells As Cell Value Changes, Excel 2000 & 2003
I put the data in B1:Cxxx.
Then formatted column A with a custom format of 000 (format|cells|number tab|custom category|000) Then I put 1 in A1 and this in A2: =IF(B2=B1,A1+1,1) and dragged down column A. As a macro: Option Explicit sub testme dim LastRow as long with worksheets("sheet9999") 'change the name here lastrow = .cells(.rows.count,"B").end(xlup).row .range("a1").value = 1 with .range("a2:a" & lastrow) .formulaR1C1 = "=IF(RC[1]=R[-1]C[1],R[-1]C+1,1)" .value = .value end with .range("a:a").numberformat = "000" end with end sub jfcby wrote: Hello, My worksheet is setup with 3 columns multiple rows varying in length. Example: Col-1 Col-2 Col-3 AIR COMP AIR COMPRESSOR CONVEYORS CONVEYORS DKFTN DRINKING FOUNTAIN DKFTN DRINKING FOUNTAIN DOM TK-EL HOT WATER TANK-ELEC EMER LIGHT EMERGENCY LIGHT EMER LIGHT EMERGENCY LIGHT EMER LIGHT EMERGENCY LIGHT I would like number Col-1 in this format: Col-1 Col-2 Col-3 001 AIR COMP AIR COMPRESSOR 001 CONVEYORS CONVEYORS 001 DKFTN DRINKING FOUNTAIN 002 DKFTN DRINKING FOUNTAIN 001 DOM TK-EL HOT WATER TANK-ELEC 001 EMER LIGHT EMERGENCY LIGHT 002 EMER LIGHT EMERGENCY LIGHT 003 EMER LIGHT EMERGENCY LIGHT Some of my worksheets have a lot of data and i would like to use a macro to insert the number for each item as example describes above. Thank you for your help, jfcby -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number Cells As Cell Value Changes, Excel 2000 & 2003
On Feb 7, 10:08 pm, Dave Peterson wrote:
I put the data in B1:Cxxx. Then formatted column A with a custom format of 000 (format|cells|number tab|custom category|000) Then I put 1 in A1 and this in A2: =IF(B2=B1,A1+1,1) and dragged down column A. As a macro: Option Explicit sub testme dim LastRow as long with worksheets("sheet9999") 'change the name here lastrow = .cells(.rows.count,"B").end(xlup).row .range("a1").value = 1 with .range("a2:a" & lastrow) .formulaR1C1 = "=IF(RC[1]=R[-1]C[1],R[-1]C+1,1)" .value = .value end with .range("a:a").numberformat = "000" end with end sub jfcby wrote: Hello, My worksheet is setup with 3 columns multiple rows varying in length. Example: Col-1 Col-2 Col-3 AIR COMP AIR COMPRESSOR CONVEYORS CONVEYORS DKFTN DRINKING FOUNTAIN DKFTN DRINKING FOUNTAIN DOM TK-EL HOT WATER TANK-ELEC EMER LIGHT EMERGENCY LIGHT EMER LIGHT EMERGENCY LIGHT EMER LIGHT EMERGENCY LIGHT I would like number Col-1 in this format: Col-1 Col-2 Col-3 001 AIR COMP AIR COMPRESSOR 001 CONVEYORS CONVEYORS 001 DKFTN DRINKING FOUNTAIN 002 DKFTN DRINKING FOUNTAIN 001 DOM TK-EL HOT WATER TANK-ELEC 001 EMER LIGHT EMERGENCY LIGHT 002 EMER LIGHT EMERGENCY LIGHT 003 EMER LIGHT EMERGENCY LIGHT Some of my worksheets have a lot of data and i would like to use a macro to insert the number for each item as example describes above. Thank you for your help, jfcby -- Dave Peterson- Hide quoted text - - Show quoted text - Hello Dave, Thank you for your help the code works great! jfcby |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete row if all cells are blank, Excel 2000 & 2003 | Excel Discussion (Misc queries) | |||
Insert Data Last Row Loop through cells Excel 2000 & 2003 | Excel Programming | |||
Does Excel 2000/2003 have a max number of records like '98 has? | Excel Discussion (Misc queries) | |||
unlocked cells in excel 2000 now locked when opened in 2003 why? | Excel Discussion (Misc queries) | |||
In Excel 2000, Cannot edit cells in worksheet created in 2003? | Excel Discussion (Misc queries) |