Using MAX,SUM and IF calcs on varyinng column lengths

ukfliers

New Member
Joined
Aug 29, 2010
Messages
4
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
ukfliers said:
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:

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

New Member
Joined
Aug 29, 2010
Messages
4
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
I'm afraid I don't quite follow.

This:

Code:
=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

New Member
Joined
Aug 29, 2010
Messages
4
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​
 
Last edited:

ukfliers

New Member
Joined
Aug 29, 2010
Messages
4
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))
 

Forum statistics

Threads
1,136,341
Messages
5,675,206
Members
419,553
Latest member
hanahass

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top