Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill down formula programmically
As the appropriate number of cells will vary the macro created will not in
my option take this into account. "Tom Ogilvy" wrote in message ... create a macro that adds the formula to the appropriate cells and saves the file as CSV. Don't prefil the formula. -- Regards, Tom Ogilvy "Pat" wrote in message ... 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill down formula programmically
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. -- Regards, Tom Ogilvy "Pat" wrote in message ... As the appropriate number of cells will vary the macro created will not in my option take this into account. "Tom Ogilvy" wrote in message ... create a macro that adds the formula to the appropriate cells and saves the file as CSV. Don't prefil the formula. -- Regards, Tom Ogilvy "Pat" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill down formula programmically
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" wrote in message ... 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. -- Regards, Tom Ogilvy "Pat" wrote in message ... As the appropriate number of cells will vary the macro created will not in my option take this into account. "Tom Ogilvy" wrote in message ... create a macro that adds the formula to the appropriate cells and saves the file as CSV. Don't prefil the formula. -- Regards, Tom Ogilvy "Pat" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill down formula programmically
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. -- Regards, Tom Ogilvy "Pat" wrote in message ... 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" wrote in message ... 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. -- Regards, Tom Ogilvy "Pat" wrote in message ... As the appropriate number of cells will vary the macro created will not in my option take this into account. "Tom Ogilvy" wrote in message ... create a macro that adds the formula to the appropriate cells and saves the file as CSV. Don't prefil the formula. -- Regards, Tom Ogilvy "Pat" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill down formula programmically
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 "Tom Ogilvy" wrote in message ... 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. -- Regards, Tom Ogilvy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating Variables Programmically | Excel Discussion (Misc queries) | |||
View previous cell value programmically | Excel Programming | |||
View previous cell value programmically | Excel Programming | |||
Edit Data Query Programmically? | Excel Programming | |||
How do I change a function's otional parameter default values programmically | Excel Programming |