Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

embedding VB code in Excel!!

Posted on 2013-06-25
4
Medium Priority
?
157 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 12

Accepted Solution

by:
mlongoh earned 1500 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 58

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Recently I finished a vbscript that I thought I'd share.  It uses a text file with a list of server names to loop through and get various status reports, then writes them all into an Excel file.  Originally it was put together for our Altiris server…
Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

636 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