Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying formating and function from one Column to another
Hi All
I am extracting data from a sql database and this is working some wha okay. I then have to format and carry out certain functions on the data, suc as making a certain column format have 2 decimal places. The way I have done it is to record a macro while doing what I want Every month I have to run a query against a sql database and dump th data into excel , I then have to run the macro to carry out th formating and function. It is sooooo hard because I constantly have t update the macro to include the new month and the whole point of thi exercise was to automate this process. I was wondering if there was not a better way of doing it. This is th code I got from the macro: Code ------------------- Sub Macro1() ' ' Macro1 Macro ' Macro recorded 2004/07/08 by ZEN6000 ' ' Range("C3:W373").Select Selection.Replace What:="", Replacement:="0.00", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Range("C:C,F:F,I:I,L:L,O:O,R:R,U:U").Select Selection.NumberFormat = "0" Range("W:W,T:T,Q:Q,N:N,K:K,H:H,E:E").Select Selection.NumberFormat = "#,##0.00" Range("D2").Select ActiveCell.FormulaR1C1 = "% Of Total" Range("D2").Select Selection.Copy Range("G2,J2,M2,P2,S2,V2").Select ActiveSheet.Paste Range("D3").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = _ "=IF(SUMIF(C[-3],RC[-3],C[-1])0,RC[-1]/SUMIF(C[-3],RC[-3],C[-1]),0)" Range("D3").Select Selection.Copy Range("G3,J3,M3,P3,S3,V3").Select ActiveSheet.Paste Range("D:D,G:G,J:J,M:M,P:P,S:S,V:V").Select Application.CutCopyMode = False Selection.NumberFormat = "0%" Range("D3").Select Selection.AutoFill Destination:=Range("D3:D373"), Type:=xlFillDefault Range("G3").Select ActiveCell.FormulaR1C1 = _ "=IF(SUMIF(C[-6],RC[-6],C[-1])0,RC[-1]/SUMIF(C[-6],RC[-6],C[-1]),0)" Selection.AutoFill Destination:=Range("G3:G373"), Type:=xlFillDefault Range("J3").Select ActiveCell.FormulaR1C1 = _ "=IF(SUMIF(C[-9],RC[-9],C[-1])0,RC[-1]/SUMIF(C[-9],RC[-9],C[-1]),0)" Selection.AutoFill Destination:=Range("J3:J373"), Type:=xlFillDefault Range("M3").Select ActiveCell.FormulaR1C1 = _ "=IF(SUMIF(C[-12],RC[-12],C[-1])0,RC[-1]/SUMIF(C[-12],RC[-12],C[-1]),0)" Selection.AutoFill Destination:=Range("M3:M373"), Type:=xlFillDefault Range("P3").Select ActiveCell.FormulaR1C1 = _ "=IF(SUMIF(C[-15],RC[-15],C[-1])0,RC[-1]/SUMIF(C[-15],RC[-15],C[-1]),0)" Selection.AutoFill Destination:=Range("P3:P373"), Type:=xlFillDefault Range("S3").Select ActiveCell.FormulaR1C1 = _ "=IF(SUMIF(C[-18],RC[-18],C[-1])0,RC[-1]/SUMIF(C[-18],RC[-18],C[-1]),0)" Selection.AutoFill Destination:=Range("S3:S373"), Type:=xlFillDefault Range("V3").Select ActiveCell.FormulaR1C1 = _ "=IF(SUMIF(C[-21],RC[-21],C[-1])0,RC[-1]/SUMIF(C[-21],RC[-21],C[-1]),0)" Selection.AutoFill Destination:=Range("V3:V373"), Type:=xlFillDefault End Sub Range("B2").Select With Selection.Font .Name = "Arial" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 6 End With Range("A2").Select ------------------- This code is so long and tedious to work thru especially when I use i with Visual Basic 6. I would really appreciate a better way of doin this Thanx Kind Regard -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying formating and function from one Column to another
It is very difficult to determine the functionality in a recorded macro
since it is written relative to the activecell position and people have no idea what your worksheet looks like or what you are trying to do. It is unclear where the new month appears - is it an additional column? Is it added rows? In any event, you can make you code dynamic, but not by recording it. You need to explain functionally what you want to do and how your data is laid out, then perhaps someone can suggest code that will work as you want. Focus on the two or three main things you want to do and then you can add the bells and whistles later after you get that running. As for changing the number format columns(i).Numberformat = "#.00" if you wanted to do it to every 3rd column starting on the 7 th column (As an example) for i = 7 to 31 step 3 columns(i).NumberFormat = "#.00" Next Using such an approach you might be able to minimize the code. -- Regards, Tom Ogilvy "poppy " wrote in message ... Hi All I am extracting data from a sql database and this is working some what okay. I then have to format and carry out certain functions on the data, such as making a certain column format have 2 decimal places. The way I have done it is to record a macro while doing what I want. Every month I have to run a query against a sql database and dump the data into excel , I then have to run the macro to carry out the formating and function. It is sooooo hard because I constantly have to update the macro to include the new month and the whole point of this exercise was to automate this process. I was wondering if there was not a better way of doing it. This is the code I got from the macro: Code: -------------------- Sub Macro1() ' ' Macro1 Macro ' Macro recorded 2004/07/08 by ZEN6000 ' ' Range("C3:W373").Select Selection.Replace What:="", Replacement:="0.00", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Range("C:C,F:F,I:I,L:L,O:O,R:R,U:U").Select Selection.NumberFormat = "0" Range("W:W,T:T,Q:Q,N:N,K:K,H:H,E:E").Select Selection.NumberFormat = "#,##0.00" Range("D2").Select ActiveCell.FormulaR1C1 = "% Of Total" Range("D2").Select Selection.Copy Range("G2,J2,M2,P2,S2,V2").Select ActiveSheet.Paste Range("D3").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = _ "=IF(SUMIF(C[-3],RC[-3],C[-1])0,RC[-1]/SUMIF(C[-3],RC[-3],C[-1]),0)" Range("D3").Select Selection.Copy Range("G3,J3,M3,P3,S3,V3").Select ActiveSheet.Paste Range("D:D,G:G,J:J,M:M,P:P,S:S,V:V").Select Application.CutCopyMode = False Selection.NumberFormat = "0%" Range("D3").Select Selection.AutoFill Destination:=Range("D3:D373"), Type:=xlFillDefault Range("G3").Select ActiveCell.FormulaR1C1 = _ "=IF(SUMIF(C[-6],RC[-6],C[-1])0,RC[-1]/SUMIF(C[-6],RC[-6],C[-1]),0)" Selection.AutoFill Destination:=Range("G3:G373"), Type:=xlFillDefault Range("J3").Select ActiveCell.FormulaR1C1 = _ "=IF(SUMIF(C[-9],RC[-9],C[-1])0,RC[-1]/SUMIF(C[-9],RC[-9],C[-1]),0)" Selection.AutoFill Destination:=Range("J3:J373"), Type:=xlFillDefault Range("M3").Select ActiveCell.FormulaR1C1 = _ "=IF(SUMIF(C[-12],RC[-12],C[-1])0,RC[-1]/SUMIF(C[-12],RC[-12],C[-1]),0)" Selection.AutoFill Destination:=Range("M3:M373"), Type:=xlFillDefault Range("P3").Select ActiveCell.FormulaR1C1 = _ "=IF(SUMIF(C[-15],RC[-15],C[-1])0,RC[-1]/SUMIF(C[-15],RC[-15],C[-1]),0)" Selection.AutoFill Destination:=Range("P3:P373"), Type:=xlFillDefault Range("S3").Select ActiveCell.FormulaR1C1 = _ "=IF(SUMIF(C[-18],RC[-18],C[-1])0,RC[-1]/SUMIF(C[-18],RC[-18],C[-1]),0)" Selection.AutoFill Destination:=Range("S3:S373"), Type:=xlFillDefault Range("V3").Select ActiveCell.FormulaR1C1 = _ "=IF(SUMIF(C[-21],RC[-21],C[-1])0,RC[-1]/SUMIF(C[-21],RC[-21],C[-1]),0)" Selection.AutoFill Destination:=Range("V3:V373"), Type:=xlFillDefault End Sub Range("B2").Select With Selection.Font .Name = "Arial" .Size = 12 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ColorIndex = 6 End With Range("A2").Select -------------------- This code is so long and tedious to work thru especially when I use it with Visual Basic 6. I would really appreciate a better way of doing this Thanx Kind Regards --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying formating and function from one Column to another
Hi Everyone
I have been on a week long holiday and only got back to the offic now. According to the reply I got from Tom Ogilvy: It is very difficult to determine the functionality in a recorde macro since it is written relative to the activecell position and peopl have no idea what your worksheet looks like or what you are trying to do. It is unclear where the new month appears - is it an additiona column? Is it added rows? In any event, you can make you code dynamic, but not by recording it You need to explain functionally what you want to do and how your data i laid out, then perhaps someone can suggest code that will work as yo want. Focus on the two or three main things you want to do and then you ca add the bells and whistles later after you get that running. I have therefore included a spreadsheet example of exactly what I nee to do. I hope this will help in making my question clearer and enable you t help me. Thanx Kind Regard Attachment filename: test.xls Download attachment: http://www.excelforum.com/attachment.php?postid=62119 -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying conditional formating | Excel Discussion (Misc queries) | |||
Copying conditional formating | Excel Discussion (Misc queries) | |||
Conditional Formating copying (Max Min) | Excel Worksheet Functions | |||
copying conditional formating | Excel Worksheet Functions | |||
copying complete column incl. formating | Excel Programming |