[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

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
0
AmmarDJ
Asked:
AmmarDJ
  • 2
1 Solution
 
mlongohCommented:
You can do what you want easily without a macro/VB code.

I'm assuming that the current values in column 2 are not needed and won't play into this.

1. Sort the first column - so that all the identical values are grouped together
2. Put a value of 1 at the top of the 2nd column
3. In the 2nd row of the 2nd column, use this formula:=IF(C2=C1,E1+1,1)
4. In the first row of the 3rd column, use this formula:=IF(LEN(E1) = 3, TEXT(E1,"0"),IF(LEN(E1)=1, "00"&E1,"0"&E1))
5. Highlight and copy the 3rd column and Paste, Special, Values into the 2nd column
6. Delete the 3rd column

That should do it.
0
 
Bill PrewCommented:
You can do this easily with a formula, see column C in the attached worksheet.  Notice you can get leading zeros just by setting the format for that column.

~bp
EE28167111.xls
0
 
AmmarDJAuthor Commented:
mlongoh,

maybe I could not get you.

I did as told but it does not help..


Regards
0
 
mlongohCommented:
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now