Code Help Please!

[mrowe]

Hi, I have a sheet of raw data that is produced from a userform, which then goes through to a worksheet as static data. The user can change data (i/e the percentage of the sale to revenue) but as there are no formulas there, I made this option to click.

The problem I'm having is it only copies the formula to L3, when I want it to copy down the whole range (which changes in length).

I'm reasonably new to coding from scratch, so any tips would be appreciated.

Thanks
Matt


Sub RecalculateRevenue()
Dim myRange As Range
Set myRange = Range("L3")
myRange.Select
myRange.FormulaR1C1 = "=RC[-2]*RC[-1]"
myRange.Copy
myRange.End(xlDown).Select
ActiveSheet.Paste



End Sub

[kkknie]

Change this line:

myRange.End(xlDown).Select

To this line:

Range(Selection, Selection.End xlDown)).Select

K

[mrowe]

Thanks, I put the line but I'm getting a complie error, expected list separator... any clues?

[kkknie]

Most of the times I get that error, it is a typo somewhere missing either a , or a )

Hope that's all it is...

K

[NateO]

Are you trying to copy this formula down column L to the extent that there are numbers in column K? If so, try:

Sub RecalculateRevenue()
Dim rw As Long
rw = [k3].End(xlDown).row
[l3] = "=J3*K3"
[l3].Copy Range("l4:l" & rw)
End Sub

Cheers, Nate

[ This Message was edited by: NateO on 2002-05-02 10:36 ]