AmmarDJ
asked on
embedding VB code in Excel!!
Hi guys.
First of all can anyone tell me how to embedd VB code in Excel.
and secondly can anyone give some clues to write a small VM code macro to embedd in excel.
I have an excel report with more than 1000 entries in two columns arranged like this -
913-ED-001 213
913-ED-001 228
913-ED-002 115
913-ED-002 116
913-ED-002 131
913-ED-002- 132
913-ED-002 133
913-ED-002 134
and so on........................ .....
I want to write a VM code macro which could update the second column as 001and increment it 002,003 ........etc for each of items in the first columns like the following.
913-ED-001 001
913-ED-001 002
913-ED-002 001
913-ED-002 002
913-ED-002 003
913-ED-002- 004
913-ED-002 005
913-ED-002 006
-----------so on
First of all can anyone tell me how to embedd VB code in Excel.
and secondly can anyone give some clues to write a small VM code macro to embedd in excel.
I have an excel report with more than 1000 entries in two columns arranged like this -
913-ED-001 213
913-ED-001 228
913-ED-002 115
913-ED-002 116
913-ED-002 131
913-ED-002- 132
913-ED-002 133
913-ED-002 134
and so on........................
I want to write a VM code macro which could update the second column as 001and increment it 002,003 ........etc for each of items in the first columns like the following.
913-ED-001 001
913-ED-001 002
913-ED-002 001
913-ED-002 002
913-ED-002 003
913-ED-002- 004
913-ED-002 005
913-ED-002 006
-----------so on
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
mlongoh,
maybe I could not get you.
I did as told but it does not help..
Regards
maybe I could not get you.
I did as told but it does not help..
Regards
Sorry, the formula was written such that C was my first column. So if the first column is A, then B1 would have a value of 1, and B2 would have this formula: =IF(A2=A1, 1+B1,1)
Copy the formula from B2 to the rest of column B (as far down as your data in column A goes).
C1's formula should be =IF(LEN(B1) = 3, TEXT(B1,"0"),IF(LEN(B1)=1, "00"&B1,"0"&B1))
and copy it to the rest of the third column.
Then select all of the values in column C, copy and Paste Special Values into column B. Then delete column C and you should be good.
Copy the formula from B2 to the rest of column B (as far down as your data in column A goes).
C1's formula should be =IF(LEN(B1) = 3, TEXT(B1,"0"),IF(LEN(B1)=1,
and copy it to the rest of the third column.
Then select all of the values in column C, copy and Paste Special Values into column B. Then delete column C and you should be good.
~bp
EE28167111.xls