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
|