i need excel macro code for spliting cell data & copy

December 22, 2011 at 08:09:32
Specs: Windows XP
Hi,

I need an Excel Macro code for split the Cell contents and copy the split data into 2 or more number of lines.

My Requirement.

Sheet1:

Column A2 : 1
Column B2 : Omega,M120,001
Column C2 : External hard drive 2.0
Column D2 : Each
Column E2 : New,New,New
Column F2 : 250GB,500GB,1TB
Column G2 : ABC Tech, IT info, Tech Shop
Column H2 : 3yrs warranty
Column I2 : 1Qty

Condition verification : i have this raw data. here first i need to split the Column B2 into Three Column and then have to verify how many item in Column E2, that much i split into new row and same thing for Column F2 & G2 and remaining data should be copy the same.

i need output like this

Sheet2 :

Column A2 : 1
Column B2 : 1
Column C2 : Omega
Column D2 : M120
Column E2 : 001
Column F2 : External hard drive 2.0
Column G2 : Each
Column H2 : New
Column I2 : 250GB
Column J2 : ABC Tech
Column K2 : 3yrs warranty
Column L2 : 1Qty

Column A2 : 1
Column B2 : 2
Column C2 : Omega
Column D2 : M120
Column E2 : 001
Column F2 : External hard drive 2.0
Column G2 : Each
Column H2 : New
Column I2 : 500GB
Column J2 : IT Info
Column K2 : 3yrs warranty
Column L2 : 1Qty

Column A2 : 1
Column B2 : 3
Column C2 : Omega
Column D2 : M120
Column E2 : 001
Column F2 : External hard drive 2.0
Column G2 : Each
Column H2 : New
Column I2 : 1TB
Column J2 : Tech Shop
Column K2 : 3yrs warranty
Column L2 : 1Qty

Please help me anyone..

Thanks & Regards
LJ



See More: i need excel macro code for spliting cell data & copy

Report •


#1
December 22, 2011 at 09:54:55
Let's start with getting the terminology right.

You used the terms Column A2, Column B2, etc.

A2, B2, etc. are Cell references, not Column references.

A, B, etc. are Columns, 2,3, etc. are Rows, A2, B2, etc. are Cells.

As for your question, the output in your example doesn't make sense.

You have requested that all of your output go into Sheet2!A2:L2. In other words, you have requested that all 3 sets of data end up in the same row (2) in Columns A through L.

That can't happen. They have to be in different rows or the last entry will overwrite the earlier ones.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 22, 2011 at 12:22:34
Hi DerbyDad03,

Sorry i was wrongly mentioned the same row number. Actually i want that in three new row A2,A3,A4 to L2,L3,L4

For Example:

RAW INPUT like this:
A B C D E F G H I
1 S.No Prod Desc UOM Cond mod Comp Warn Qty

2 1 HP,M1,01 Pendrive Each New,New, 4gb,8gb Abc inc,IT inc 3yrs 1


OUTPUT i want like this:

A B C D E F G H I J K L
1 S.No Ref Prod Part ver Desc UOM Cond mod Comp Warn Qty

2 1 1 HP M1 01 Pendrive Each New 4gb Abc inc 3yrs 1

3 1 2 HP M1 01 Pendrive Each New 4gb IT inc 3yrs 1


Thanks
LJ


Report •

#3
December 22, 2011 at 15:42:26
Please click on the blue line at the end of this post and read the instructions on how to post example data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 26, 2011 at 02:56:02
Hi,

I need an Excel Macro code for split the Cell contents and copy the split data into 2 or more number of lines.

Requirement:
1. Input - Sheet1 and output - Sheet2
2. In Input data one line have multiple lines data. we need to split into new line.
3. Column"Prod" has to split into three column.
4. Based on "Cond" column have to create new line. if it has two data means like as"New,New".we need to split into Two new line.
5. "Mod" & "comp" having two data in one line.split it into new lines.
6. "Ref" it's referring the how many lines having that product.
7. Remain everything have to copy.


My Input is :

S.No  Prod   Desc    UOM   Cond     mod      Comp         Warn   Qty
1  HP,M1,01 Pendrive Each New,New  4gb,8gb Abc inc,IT inc 3yrs   1 

I want OUTPUT like as:

S.No  Ref  Prod  Part  ver  Desc      UOM  Cond  mod   Comp   Warn   Qty 
1     1     HP    M1   01   Pendrive  Each  New  4gb  Abc inc  3yrs  1 
1     2     HP    M1   01   Pendrive  Each  New  8gb  IT inc   3yrs  1


Thanks you in advance.

LJ.


Report •


Ask Question