Hi Harpreet,
Here it comes... I made a new sheet "test" to test it, but you can
change that statement in
cCooked = "MODIFIED DATA"
after testing, of course.
Feedback is welcome.
Kind regards,
Paul Willekens
'=======================================================================
Sub CookData()
Dim cContent
Dim cCooked
Dim cHouse
Dim cName
Dim cRaw
Dim cType
Dim dDate
Dim nEmpty
Dim nRepurchase
Dim nRowRaw
Dim nRowCooked
Dim nNetValue
Dim nSales
cRaw = "RAW DATA"
nRowRaw = 3
cCooked = "test"
nRowCooked = 2
Sheets(cRaw).Select
While nEmpty < 3
cContent = Trim(Cells(nRowRaw, 1).Value)
If Len(cContent) > 0 Then 'not an empty line
nEmpty = 0
If IsNumeric(cContent) Then 'scheme ID
'Scheme Name
cName = Cells(nRowRaw, 2)
'Net Asset Value
nNetValue = Cells(nRowRaw, 3)
'Repurchase Price
nRepurchase = Cells(nRowRaw, 4)
'Sale Price
nSales = Cells(nRowRaw, 5)
'Date
dDate = Cells(nRowRaw, 6)
Sheets(cCooked).Select
Cells(nRowCooked, 1).Value = cHouse
Cells(nRowCooked, 2).Value = cType
Cells(nRowCooked, 3).Value = cContent
Cells(nRowCooked, 4).Value = cName
Cells(nRowCooked, 5).Value = nNetValue
Cells(nRowCooked, 6).Value = nRepurchase
Cells(nRowCooked, 7).Value = nSales
Cells(nRowCooked, 8).Value = dDate
nRowCooked = nRowCooked + 1
Sheets(cRaw).Select
Else 'either a new Mutual Fund House or a new Type
If Right(cContent, 1) = ")" Then 'new Type
cType = cContent
cHouse = ""
Else 'new Mutual Fund House
cHouse = cContent
End If
End If
Else
nEmpty = nEmpty + 1
End If
nRowRaw = nRowRaw + 1
Wend
End Sub
'=======================================================================
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
To unsubscribe, reply using "remove me" as the subject.
