Using MAX,SUM and IF calcs on varyinng column lengths

[ukfliers]

Hi,
I have created a macro to calculate the earliest date using max, also the sum of the columns and and if statement to stop throwing an error if divide by zero occurs.

My problem is that I do these calculations on database dump into a worksheet. Each time this dump occurs the number of rows in the worksheet vary and that is where I am stuck.

For instance my MAX calculation is based on relative positioning and looks like this "=MAX(R[-7]C:R[-1]C)"

It is always R[-1]C as it calculates to the current row where the answers appear, hence [-1], and from row 2 as headings are in row 1. The above example has 8 rows of data.

What I do right now is - before I run macro is do a search on -7 and replace with -28 if there are 29 rows etc.

What I want is a complete macro...thanks in advance.

 

[DonkeyOte]

It is always R[-1]C as it calculates to the current row where the answers appear, hence [-1], and from row 2 as headings are in row 1. The above example has 8 rows of data.

What I do right now is - before I run macro is do a search on -7 and replace with -28 if there are 29 rows etc.

Far better to use:

R2C:R[-1]C

If the number following RC is not encased within [ ] then it is an absolute rather then relative reference and given your data always starts from row 2 that value can be an absolute reference.

 

[ukfliers]

That worked perfectly - thank you so much. Now I would like to test you on my same macro for the last piece... I have identified the cell wit a dim statement Dim rl4 as Range The line you helped me with is now rl4.FormulaR1C1="IF(RC[-2]=0,RC[-1]/RC[-2])" From that line I need to select the whole range from rl4 to RC2 coping the formula. No matter what I try it doesn't work. I tried Selection.AutoFill.Destination=Range(Range("rl4"),Range("rl4").offset(-79,0))).Type=xlFillDefault Taking it mainly from the macro recorder.

 

[DonkeyOte]

I'm afraid I don't quite follow.

This:

=IF(RC[-2]=0,RC[-1]/RC[-2])

would only ever generate #DIV/0! or FALSE
(note: you're missing the = operator prior to the IF in your code)

Perhaps you could outline the specifics of the code ?
For ex. is RL4 a physical range reference (ie XL2007+) or a range variable ?
The code snippets posted are I'm afraid rather ambiguous.

 

[ukfliers]

Sorry for being ambiguous - new at this and may not be doing this in the perfect manner.
I have several columns that are extracted from a database. Two of those columns perform calculations on modules in the database and return data about objects.
Columns A thru O Each time I run my extraction
The amount of rows always varies but columns remain the same. Column A holds module name.
Other columns hold data. I find the first blank row in column A (there are no blank rows between data) and set it to a variable cl
I insert a column after D and name the column heading % Deleted objects (row 1 is column headings)
I then perform calculations on all columns that show the totals in the last blank row adjacent to cl.
cl has the word "Totals" inserted.
So I have declared a variable for each of the cells in the row adjacent to cl and set those variables as follows:
Set rl1=cl.offset(0,1)
rl1.FormulaR1C1="=MAX(R2C:R[-1]C)" you supplied the R2C
rl1.NumberFormat="dd/mm/yy"
I do this now for each column thru P as I added a column.
Column C is number of objects
Column D is number of deleted objects (subset)
Column E is my inserted column % Deleted objects. here are the calculations:
Column C Set rl2=cl.offset(0,2)
rl2.FormulaR1C1="=SUM(R2c:R[-1]C)"
rl2.NumberFormat="#,##0"

Column D Set rl3=cl.offset(0,3)
rl3.FormulaR1C1="=SUM(R2c:R[-1]C)"
rl3.NumberFormat="#,##0"

Column E Set rl4=cl.offset(0,4)
rl4.FormulaR1C1="=IF(RC[-2]=0,,RC[-1]/RC[-2])"
rl4.NumberFormat="0%"

Now it works no matter how bad it is programmed, but what I cant get to work is copying the rl4 formula into all cells of column E except row 1 Right now I finish the macro and grab rl4 in the corner and drag copy to E2​

 

[ukfliers]

Hey DonkeyOte,

What do you think of this - it works, don't know whether it is neat.

Created another variable set it to the cell I want to copy and...

clE.Select
Selection.Copy Destination:=Range(Selection.Offset(-1, 0), Selection.End(xlUp).Offset(1, 0))