Solved

embedding VB code in Excel!!

Posted on 2013-06-25
4
150 Views
Last Modified: 2013-09-02
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
Comment
Question by:AmmarDJ
  • 2
4 Comments
 
LVL 12

Accepted Solution

by:
mlongoh earned 500 total points
ID: 39275143
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
 
LVL 52

Expert Comment

by:Bill Prew
ID: 39275185
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
 

Author Comment

by:AmmarDJ
ID: 39276003
mlongoh,

maybe I could not get you.

I did as told but it does not help..


Regards
0
 
LVL 12

Expert Comment

by:mlongoh
ID: 39277863
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an addendum to the following article: Acitve Directory based Outlook Signature (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_24950055.html) The script is fine, and works in normal client-server domains…
In this article we want to have a look at the directory attributes which are used by Microsoft to store the so called Security Identifiers (SID). These SIDs plays an important role in delegating and granting permissions and in authentication of trus…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now