Fill down formula programmically

[Pat]

Can anyone tell me if is possible to fill down a formula as and when
required programmically.
I will try to explain what I mean.
What I am currently doing is saving a .xls file as a .csv file then
importing that file into another program which I do regularly. The .csv file
has extra rows containing a formula that usually contain no data. The
reason for having more of the same formula that may not be used at that time
when saving is that it avoids having to check the .csv file to determine if
all data has been saved in the new format. The problem I am experiencing is
that it causes problems in the program I am importing into.

Hope someone can show me a way around this.

Thanking you.
Pat

 

[Tom Ogilvy]

create a macro that adds the formula to the appropriate cells and saves
the file as CSV.

Don't prefil the formula.

 

[Pat]

As the appropriate number of cells will vary the macro created will not in
my option take this into account.

 

[Tom Ogilvy]

If you design the macro to determine that, it certainly will.

set rng = Range("A1").CurrentRegion
set rng = rng.resize(,1)
rng.offset(0,10).Formula = "=Sum(B1:G1")

as an example.

 

[Pat]

Well Tom, here I thought a macro was simply a series of actions that were
performed to create the code to run over and over again.
What you are talking about is something beyond my capabilities, writing
complex code. Would it be asking too much to give me an example where I
could possibly modify for my own particular requirements? Or is there
sample code on the web that you could point me too?

Regards
Pat

 

[Tom Ogilvy]

I just did. If you can't modify that, then you need to specifically explain
where your data is, where you want the formula and what you want the formula
to be.

for the example, the description is that my data starts in A1 with no header
row. I want to put a formula that sums columns B to G and that formula
would be in column K

so the code

Dim rng as Range
set rng = Range("A1").CurrentRegion
' rng now refers to the area with data surrounding
' cell A1. This would be the same as if you clicked in
' A1 and did Ctrl+Shift+8
set rng = rng.resize(,1)
' now the variable rng is adjusted just to refer to
' column A for as many rows as were in the
' region around A1.
rng.offset(0,10).Formula = "=Sum(B1:G1)"
'Now the formula =Sum(B1:G1) (typo corrected)
' is placed in K1 down to the number of rows
' around cell A1. Since the addresses are relative
' excel automatically adjusts the references.

 

[Pat]

Thank you for replying.

Details of what I want to achieve are explained in the following example:
(Apologizes if I give too much detail)

Sheet10 in Book1 is where the data is contained.
I will firstly need to explain in detail what Sheet10 contains.

Sheet10 contains a formula in the range D8:J1000 and is as follows:
D8 =Sheet6!L30 this is filled down to D1000
E8 =Sheet6!R30 this is filled down to E1000
F8 =Sheet6!C30 this is filled down to F1000
G8 =Sheet6!H30 this is filled down to G1000
H8 =Sheet6!T30 this is filled down to H1000
I8 =Sheet6!U30 this is filled down to I1000
J8 =Sheet6!D30 this is filled down to J1000

Not all rows down to r1000 will contain data as this is determined what data
there is in Sheet6.
One exception will be J8:J1000 as this will always contain data.

The most important cut off point is column D Where the data ends in column
D is where the cut off should be.
Data in any other column E:J below where data ends in column D can be
regarded as not important and can be cut off.

Now to saving the data as a .CSV file;
I currently use the following macro:

Sub Mac1()
Application.DisplayAlerts = False
Workbooks.Open Filename:="C:\My Documents\Excel doc\Excel
books\Prepare.xls"
Calculate
ActiveWorkbook.SaveAs Filename:="C:\HLSwin\Ready.CSV" _
, FileFormat:=xlCSV, CreateBackup:=False
Application.DisplayAlerts = True
Calculate
Windows("Book1.xls").Activate
Range("I640").Select
Calculate
End Sub

In the workbook Prepare.xls sheet1 there contains a formula in the range
A1:G1000 and is as follows:
A1 ='[Book1.xls]Sheet10'!$D8 this is filled down to A1000
B1 ='[Book1.xls]Sheet10'!$E8 this is filled down to B1000
C1 ='[Book1.xls]Sheet10'!$F8 this is filled down to C1000
D1 ='[Book1.xls]Sheet10'!$G8 this is filled down to D1000
E1 ='[Book1.xls]Sheet10'!$H8 this is filled down to E1000
F1 ='[Book1.xls]Sheet10'!$I8 this is filled down to F1000
G1 ='[Book1.xls]Sheet10'!$J8 this is filled down to G1000

Like in Book1 Sheet10 there is data below in the rows where data ends in
columnA

So there you have it, I hope you can see where I am coming.

Regards
Pat