Home |
Search |
Today's Posts |
#1
|
|||
|
|||
designing a macro
hi im working for a compnay that requires me to generate reports on a monthly basis from raw files sent from the client. i was recently tasked to design a macro that is able to sort the data and put them in the right places which i have difficulty with im hoping someone here would be able to help me out.
so if u look at the attached file. in sheet 2 is a example of my raw data which i have to input into sheet 1 its pretty simple firstly i need to put redemption nominee account values into cash tab in sheet 1 for eg. sheet 2 c2 and d2 to be pasted into sheet 1 L3 and Q3 respectively. i could record a macro that copy paste whatever i do but as each month the amt of redemption and subscription varies how do i make it identify whether isit firstly a redemption or subscription then by the type whether isit CPFOA or SRS or Cash and input them into the right columns accordingly. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
designing a macro
On Wednesday, September 26, 2012 10:40:00 PM UTC-5, Za1 wrote:
hi im working for a compnay that requires me to generate reports on a monthly basis from raw files sent from the client. i was recently tasked to design a macro that is able to sort the data and put them in the right places which i have difficulty with im hoping someone here would be able to help me out. so if u look at the attached file. in sheet 2 is a example of my raw data which i have to input into sheet 1 its pretty simple firstly i need to put redemption nominee account values into cash tab in sheet 1 for eg. sheet 2 c2 and d2 to be pasted into sheet 1 L3 and Q3 respectively. i could record a macro that copy paste whatever i do but as each month the amt of redemption and subscription varies how do i make it identify whether isit firstly a redemption or subscription then by the type whether isit CPFOA or SRS or Cash and input them into the right columns accordingly. +-------------------------------------------------------------------+ |Filename: New Microsoft Excel Worksheet.zip | |Download: http://www.excelbanter.com/attachment.php?attachmentid=604| +-------------------------------------------------------------------+ -- Za1 Be more specific: sheet2!c2 sheet1!__ because etc |
#3
|
|||
|
|||
Quote:
sheet2!c2sheet1!L3 (under cash cos nominee account under redemption cos red) sheet2!d2sheet1!q3(under cash cos nominee account under redemption cos red) sheet2!c3sheet1!L4(under cash cos nominee account under redemption cos red) sheet2!d3sheet1!q4(under cash cos nominee account under redemption cos red) sheet2!c4sheet1!j5(under CPFOA cos CPFOA under redemption cos red) sheet2!d4sheet1!o5(under CPFOA cos CPFOA under redemption cos red) sheet2!c5sheet1!j6(under CPFOA cos CPFOA under redemption cos red) sheet2!d5sheet1!o6(under CPFOA cos CPFOA under redemption cos red) sheet2!c6sheet1!k7(under SRS cos SRS under redemption cos red) sheet2!d6sheet1!p7(under SRS cos SRS under redemption cos red) sheet2!c7sheet1!k8(under SRS cos SRS under redemption cos red) sheet2!d7sheet1!p8(under SRS cos SRS under redemption cos red) sheet2!c8sheet1!c9(under cash cos nominee account under subcription cos sub) sheet2!d8sheet1!h9(under cash cos nominee account under subcription cos sub) sheet2!c9sheet1!c10(under cash cos nominee account under subcription cos sub) sheet2!d9sheet1!h10(under cash cos nominee account under subcription cos sub) sheet2!c10sheet1!c11(under cash cos nominee account under subcription cos sub) sheet2!d10sheet1!h11(under cash cos nominee account under subcription cos sub) sheet2!c11sheet1!a12(under CPFOA cos CPFOA under subcription cos sub) sheet2!d11sheet1!f12(under CPFOA cos CPFOA under subcription cos sub) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
designing a macro
On Wednesday, September 26, 2012 10:40:00 PM UTC-5, Za1 wrote:
hi im working for a compnay that requires me to generate reports on a monthly basis from raw files sent from the client. i was recently tasked to design a macro that is able to sort the data and put them in the right places which i have difficulty with im hoping someone here would be able to help me out. so if u look at the attached file. in sheet 2 is a example of my raw data which i have to input into sheet 1 its pretty simple firstly i need to put redemption nominee account values into cash tab in sheet 1 for eg. sheet 2 c2 and d2 to be pasted into sheet 1 L3 and Q3 respectively. i could record a macro that copy paste whatever i do but as each month the amt of redemption and subscription varies how do i make it identify whether isit firstly a redemption or subscription then by the type whether isit CPFOA or SRS or Cash and input them into the right columns accordingly. +-------------------------------------------------------------------+ |Filename: New Microsoft Excel Worksheet.zip | |Download: http://www.excelbanter.com/attachment.php?attachmentid=604| +-------------------------------------------------------------------+ -- Za1 try '=========== Option Explicit Sub GetDataSAS() Dim ds As Worksheet Dim ss As Worksheet Dim lr As Long Dim c As Range Dim mc As Integer Dim x As Integer Set ds = Sheet1 Set ss = Sheet2 lr = ss.Cells(Rows.Count, 1).End(xlUp).Row For Each c In ss.Range("b2:b" & lr) If UCase(c) = "SUB" Then mc = 0 Else mc = 10 End If Select Case c.Offset(, -1) Case "CFPOA": x = 0 Case "SRS": x = 1 Case "Nominee Account": x = 3 Case Else End Select c.Offset(, 1).Copy ds.Cells(Rows.Count, mc + x).End(xlUp)(2) c.Offset(, 2).Copy ds.Cells(Rows.Count, mc + x + 4).End(xlUp)(2) Next c ds.Columns.AutoFit End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Designing Form | New Users to Excel | |||
Macro Designing | Excel Discussion (Misc queries) | |||
How to us macro for designing a statistic table (i.e soccer score table) auto update after i enter the game result | Excel Programming | |||
Designing a spreadsheet | Excel Programming | |||
Designing a Form | Excel Discussion (Misc queries) |