Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Code Help Please!

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    232
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Change this line:

    myRange.End(xlDown).Select

    To this line:

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

    K



  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    232
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •