Code Help Please!

mrowe

Board Regular
Joined
Feb 17, 2002
Messages
232
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Change this line:

myRange.End(xlDown).Select

To this line:

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

K
 
Upvote 0
Thanks, I put the line but I'm getting a complie error, expected list separator... any clues?
 
Upvote 0
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
 
Upvote 0
Are you trying to copy this formula down column L to the extent that there are numbers in column K? If so, try:

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

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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
Back
Top