Solved

embedding VB code in Excel!!

Posted on 2013-06-25
4
152 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 53

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

860 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