Variable Ranges

[Paddy1979]

Hello All,

Can you help me please

I have the below macro which i plan to autofill down 100 rows, the trouble is it takes too long because i have set the SumProduct range of 65000 rows. The reason for that is the number of rows of data on the Jobs worksheet will change daily so i know this will cover them.

Can i name the range at the start of the macro without entering row numbers, ? ie Range is start of column I to end of data in column I??

Here is the code.

With ActiveSheet


With Range("F2")


.FormulaR1C1 = "=SUMPRODUCT(--(JOBS!R1C14:R65535C14=Sheet1!RC2),--(JOBS!R1C12:R65535C12=Sheet1!R1C))"
.Offset(0, 1).FormulaR1C1 = "=SUMPRODUCT(--(JOBS!R1C14:R65535C14=Sheet1!RC2),--(JOBS!R1C12:R65535C12=Sheet1!R1C))"
.Offset(0, 2).FormulaR1C1 = "=SUMPRODUCT(--(JOBS!R1C14:R65535C14=Sheet1!RC2),--(JOBS!R1C12:R65535C12=Sheet1!R1C))"
.Offset(0, 3).FormulaR1C1 = "=SUMPRODUCT(--(JOBS!R1C14:R65535C14=Sheet1!RC2),--(JOBS!R1C12:R65535C12=Sheet1!R1C))"
.Offset(0, 4).FormulaR1C1 = "=SUMPRODUCT(--(JOBS!R1C14:R65535C14=Sheet1!RC2),--(JOBS!R1C12:R65535C12=Sheet1!R1C))"
.Offset(0, 5).FormulaR1C1 = "=SUM(RC[-5]:RC[-1])"
.Offset(0, 6).FormulaR1C1 = "=IF(RC[-6]<1,0,RC[-6]/RC[-1])"
.Offset(0, 7).FormulaR1C1 = "=IF(RC[-7]<1,0,RC[-7]/(RC[-2]-RC[-3]))"

Man Thanks

 

[texasalynn]

add this:

LR = ActiveSheet.Range("A65536").End(xlUp).Row

then change your formulas (only did one as example)

.FormulaR1C1 = "=SUMPRODUCT(--(JOBS!R1C14:R" & LR & "C14=Sheet1!RC2),--(JOBS!R1C12:R" & LR & "C12=Sheet1!R1C))"

 

[Paddy1979]

cheers texas , buy that man a drink