Try :
Sub FromTable1()
Dim Rg As Range, Tabl, lgRow As Long
Set Rg = Range([B6], Cells(6, 2).End(xlDown)).Resize(, 5)
Tabl = Rg
lgRow = 5
For i = 1 To UBound(Tabl)
lgRow = lgRow + 1
Cells(lgRow, 10).Resize(3) = Tabl(i, 1)
Cells(lgRow, 11).Resize(3) = Tabl(i, 2)
Cells(lgRow, 12) = "X1"
Cells(lgRow, 12).AutoFill Cells(lgRow, 12).Resize(3)
Cells(lgRow, 13) = Tabl(i, 3)
lgRow = lgRow + 1
Cells(lgRow, 13) = Tabl(i, 4)
lgRow = lgRow + 1
Cells(lgRow, 13) = Tabl(i, 5)
Next i
End Sub
Or
Sub FromTable2()
Dim Rg As Range, Tabl, lgRow As Long
Set Rg = Range([C20], Cells(20, 3).End(xlDown)).Offset(, -1).Resize(, 5)
For Each c In Rg.Resize(, 1)
If c.Value = "" Then c.Value = c.Offset(-1).Value
Next c
Tabl = Rg
lgRow = 5
For i = 1 To UBound(Tabl)
lgRow = lgRow + 1
Cells(lgRow, 10).Resize(3) = Tabl(i, 1)
Cells(lgRow, 11).Resize(3) = Tabl(i, 2)
Cells(lgRow, 12) = "X1"
Cells(lgRow, 12).AutoFill Cells(lgRow, 12).Resize(3)
Cells(lgRow, 13) = Tabl(i, 3)
lgRow = lgRow + 1
Cells(lgRow, 13) = Tabl(i, 4)
lgRow = lgRow + 1
Cells(lgRow, 13) = Tabl(i, 5)
Next i
End Sub
Regards.
Daniel
De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De
la part de Bhargava Raju
Envoyé : lundi 22 août 2011 11:32
À : excel-macros@googlegroups.com
Objet : $$Excel-Macros$$ Help ASAP
Dear experts,
I need help in excel ASAP. The attached is a excel file which contains data.
I usually given Table 1 or Table 2 and needs to be changed to the Table 3
format. The data in the attached file is very less. But in real time I would
be given data containing some 5000 to 8000 line items with 60 to 80 columns.
--
Regards,
Bhargava Raju D
--
----------------------------------------------------------------------------
------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
