Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Formula check

  1. #1
    Board Regular
    Join Date
    May 2019
    Posts
    86
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Formula check

    I have this formula

    Sub w3202L()
    '
    ' w3202L Macro
    '


    '
    Sheets("Misc").Select
    Range("AA6").Select
    ActiveCell.FormulaR1C1 = "=(R[1]C[-13])"
    Range("AA6").Select
    Selection.Copy
    Range("AA6:AA370").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Range("AA11").Select
    End Sub

    I need AA6 to be N6 and it comes out N7

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,799
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Formula check

    Try changing:
    Code:
    "=(R[1]C[-13])"
    which offsets the row count by 1 to:
    Code:
    "=(RC[-13])"

    Note you can also speed up your code and shorten by getting rid of most your Select statements. They are not neceesary.
    This:
    Code:
    Range("AA6").Select
    ActiveCell.FormulaR1C1 = "=(RC[-13])"
    can be simplified to this:
    Code:
    Range("AA6").FormulaR1C1 = "=(RC[-13])"

    And this:
    Code:
    Range("AA6").Select
    Selection.Copy
    can be simplified to:
    Code:
    Range("AA6").Copy
    etc, etc
    Last edited by Joe4; Aug 22nd, 2019 at 02:55 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,298
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Formula check

    How about
    Code:
    Sub w3202L()
    Sheets("Misc").Select
    Range("AA6:AA370").FormulaR1C1 = "=(RC[-13])"
    Range("AA11").Select
    End Sub
    I'm definitely getting slow today
    Last edited by Fluff; Aug 22nd, 2019 at 02:57 PM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular
    Join Date
    May 2019
    Posts
    86
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula check

    TY Everyone this is working well......I did 4 different formulas from the statement.
    How can i create pull down menu to select different ones?

  5. #5
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,799
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Formula check

    How can i create pull down menu to select different ones?
    I do not know what you mean. Please explain in more detail.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    Board Regular
    Join Date
    May 2019
    Posts
    86
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula check

    so i have used 4 different scripts i would like to make a drop down menu to select which script to use....tyvm
    1)Sub w3202L()xxxxxxxx
    2)Sub w3201L()xxxxxxxx
    3)Sub w3102L()xxxxxxxx
    4)Sub w3101L()xxxxxxxx
    I

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,298
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Formula check

    Is your dropdown a Data Validation dropdown?
    If so what cell is it in?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular
    Join Date
    May 2019
    Posts
    86
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula check

    No
    these are inside a module 9 , I want to create a drop down menu using thes 4 different formulas

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,298
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Formula check

    I'm afraid I don't understand, where is your dropdown menu & what type of drop down is it?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular
    Join Date
    May 2019
    Posts
    86
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula check

    I have not created oned yet I want to know the best way to do this .....ty

Some videos you may like

User Tag List

Tags for this Thread

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
  •