Copying formating and function from one Column to another

P

poppy

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:confused:

Thanx

Kind Regard
 
T

Tom Ogilvy

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 > said:
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:confused:

Thanx

Kind Regards
Click to expand...
 
P

poppy

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.
Click to expand...

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

macro to copy rows that contain a cell with a certain number 2
Macro Crashing 3
same macro on multiple sheets 4
Macro that deletes and pastes filterd rows and then some 2
Modify range in VBA 9
File Name changes with dates and Deleting Rows of Zeros 0
fixing a run-time error'9': Subscript out of range 0
"=TEXT(RC[-1],"MMM")" 4

Top