Solved

embedding VB code in Excel!!

Posted on 2013-06-25
4
149 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 51

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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…
Welcome back!  My apologies for taking so long to write part two of this series; it's been a long time coming!  As I promised in Part 1, this article will focus on how to locate those elusive AD properties that you are searching for.  Why is this us…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

759 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

21 Experts available now in Live!

Get 1:1 Help Now