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/