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

Thread: Copy formula
Thanks Thanks: 0 Likes Likes: 0

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

    Default Copy formula

    I have a simple formula starting in column AD6-AQ6
    in AD6 i have =aa06 AE6 =aa07 AF6 =aa08 ect
    it stops in cell AQ6 then drops down to AD7 over to AQ7 then down to AD8-AQ8
    when i copy cell AD6 and choose the rest of the cells (ae6-aq320) the formula changes from AA to AB
    how can i keep it all AA and not change to AB or AC ect

  2. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy formula

    Maybe change
    =AA6
    to
    =$AA6

    ?
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  3. #3
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,362
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Copy formula

    Hi,

    In cell AD6 you could have following formula:

    Code:
    =OFFSET($AA$6,COLUMN()-30,0)
    which can then be copied from AD6 till AQ6

    Hope this will help

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

    Default Re: Copy formula

    ty but didnt work

  5. #5
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy formula

    Which one didn't work, you've been offered two solutions ?

    And in what way exactly did it not work ?
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

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

    Default Re: Copy formula

    fIRST LINE IS ALL $AA6
    second is $AA7 ect

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

    Default Re: Copy formula

    did not copy right filled each line the same formula

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

    Default Re: Copy formula

    I think Gerald missed the first part of your question, and didn't notice that you are wanting to copy the formula across the row, but you actually want the column piece to increment. His solution shows you how to lock down the column reference all right, but won't increment the row part.

    James formula will do exactly what you want for the first row.

    However, I am not sure what you want the formula to look like in the second row. What formula do you expect to see in AD7?
    Last edited by Joe4; Sep 5th, 2019 at 09:02 AM.
    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!"

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

    Default Re: Copy formula

    If the formula in AD7 should be pulling from AA20, then try this modication to James's formula:
    Code:
    =OFFSET($AA$6,(COLUMN()-30)+(ROW()-6)*14,0)
    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!"

  10. #10
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy formula

    Joe4, you're right, I missed the row increment part, apologies to all !
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

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
  •