Help with an autofill macro

[markdenovan]

Hi All,

I have a little problem which I hope someone can help me with.

I use the following formula to work out how long a list is (it's never the same number of rows):

ActiveSheet.UsedRange.Select
irows = Selection.Rows.Count

I then put in a couple of formulas in the columns next to it which works fine.

I then try to autofill using the following code:

Range("C3:E3").Select
Selection.AutoFill Destination:=Range("C3:E(irows)"), Type:=xlFillDefault
Range("C3:E(irows)").Select

I obviously have the wrong syntax, as the macro stops. If I replace the (irows) with the actual row number it works fine.

Can someone please put me straight?

Many thanks,

Mark

 

[Clares1]

Hi

Have you tried using this method:

Range("G1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 3).Select
ActiveCell.FormulaR1C1 = "=(RC[-1]-RC[-7])/RC[-1]"
Selection.Copy
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste

Subtitute for your own cell references

 

[Mudface]

Your syntax for Range("C3:E(irows)") should be-

Range("C3:E" & irows)

 

[markdenovan]

Many thanks you two, worked great.

Mark