Dynamic range for autofill macro

[Guest]

I have a data sheet where I have to add a formula to the last 2 columns. The
number of rows changes with each new data sheet and can be several thousand
rows.

The sheet is called "JobLabour". The last column is Y and the data starts
below the heading "Line Property" at "Y7" (this could change, although I have
control over the report format).

I enter a formula (from the macro code) ,
"=IF(RC[-13]=""wlpn"",80,IF(RC[-13]=""2alpn"",44,IF(RC[-13]=""1alpn"",36,IF(RC[-13]=""3alpn"",60,IF(RC[-13]=""4alpn"",71,80)))))" at cell Z8.

I have the formula, "=RC[-8]*RC[-1]" in cell AA8.

I need to copy this down to the last populated value in col "Y".

Is there code that will do this automatically when new data is pasted into
sheet "JobLabour"?

I currently do this via a macro, but users often forget to run it and I have
to set the number of rows to fill.

 

[Dave Peterson]

Dim LastRow as long
with worksheets("JobLabour")
Lastrow = .cells(.rows.count,"Y").end(xlup).row
.range("AA8:AA" & lastrow).formular1c1 = "=RC[-8]*RC[-1]"
end with

=======
You could try toggling a setting:
tools|Options|edit tab|Extend data range formats and formulas

But I think I'd provide a macro that inserted the row and filled the formulas.

David McRitchie shares some code:
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
look for: InsertRowsAndFillFormulas

I have a data sheet where I have to add a formula to the last 2 columns. The
number of rows changes with each new data sheet and can be several thousand
rows.

The sheet is called "JobLabour". The last column is Y and the data starts
below the heading "Line Property" at "Y7" (this could change, although I have
control over the report format).

I enter a formula (from the macro code) ,
"=IF(RC[-13]=""wlpn"",80,IF(RC[-13]=""2alpn"",44,IF(RC[-13]=""1alpn"",36,IF(RC[-13]=""3alpn"",60,IF(RC[-13]=""4alpn"",71,80)))))" at cell Z8.

I have the formula, "=RC[-8]*RC[-1]" in cell AA8.

I need to copy this down to the last populated value in col "Y".

Is there code that will do this automatically when new data is pasted into
sheet "JobLabour"?

I currently do this via a macro, but users often forget to run it and I have
to set the number of rows to fill.

 

[Guest]

Thanks Dave, that's exactly what I needed. I've been able to adopt this to
similar situations.


--
Jim

Dim LastRow as long
with worksheets("JobLabour")
Lastrow = .cells(.rows.count,"Y").end(xlup).row
.range("AA8:AA" & lastrow).formular1c1 = "=RC[-8]*RC[-1]"
end with

=======
You could try toggling a setting:
tools|Options|edit tab|Extend data range formats and formulas

But I think I'd provide a macro that inserted the row and filled the formulas.

David McRitchie shares some code:
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
look for: InsertRowsAndFillFormulas

I have a data sheet where I have to add a formula to the last 2 columns. The
number of rows changes with each new data sheet and can be several thousand
rows.

The sheet is called "JobLabour". The last column is Y and the data starts
below the heading "Line Property" at "Y7" (this could change, although I have
control over the report format).

I enter a formula (from the macro code) ,
"=IF(RC[-13]=""wlpn"",80,IF(RC[-13]=""2alpn"",44,IF(RC[-13]=""1alpn"",36,IF(RC[-13]=""3alpn"",60,IF(RC[-13]=""4alpn"",71,80)))))" at cell Z8.

I have the formula, "=RC[-8]*RC[-1]" in cell AA8.

I need to copy this down to the last populated value in col "Y".

Is there code that will do this automatically when new data is pasted into
sheet "JobLabour"?

I currently do this via a macro, but users often forget to run it and I have
to set the number of rows to fill.