Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Apr 2015
    Posts
    23
    Thanks
    11
    Thanked 0 Times in 0 Posts

    Auto fill Formula

    Hi,

    This macro formula is perfect but while AutoFill Destination:=Range("J2:L15") it ends at L15 I want to set it AutoFill to lastRow, Copy Range from L2 to LastRow and PasteSpecial xlPasteValues in Range G2.

    Code:
    Sub CopyFormulas()
    With ActiveSheet
        .Range("J2").Formula = "=IF(C2=10," & """1010""" & ",IF(C2=11," & """1111""" & "," & """1414""" & "))"
        .Range("K2").Formula = "=RIGHT(CONCATENATE(" & """00000000""" & ",D2),8)"
        .Range("L2").Formula = "=CONCATENATE(I2,J2,K2)"
        .Range("J2:L2").AutoFill Destination:=Range("J2:L15"), Type:=xlFillDefault
        .Range("L2:L15").Copy
        .Range("G2:G15").PasteSpecial xlPasteValues
    
    
    End With
    End Sub
    Thanks in advance.

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,207
    Thanks
    49
    Thanked 989 Times in 919 Posts
    What do you want to base the "last row" on? Is it a particular column?

    cheers, Paul

  3. #3
    New Lounger
    Join Date
    Apr 2015
    Posts
    23
    Thanks
    11
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Paul T View Post
    What do you want to base the "last row" on? Is it a particular column?

    cheers, Paul
    Yes on Column A.
    Thanks

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Danny,

    This should do the trick:

    .Range("J2:L2").AutoFill Destination:=Range("J2:L" & Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillDefault


    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. The Following User Says Thank You to RetiredGeek For This Useful Post:

    danny69 (2016-01-05)

  6. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    And then:
    Code:
    With .Range("L2:L" & LR)
       .Offset(, -5).Value2 = .Value2
    End With
    Regards,
    Rory

    Microsoft MVP - Excel

  7. The Following User Says Thank You to rory For This Useful Post:

    danny69 (2016-01-05)

  8. #6
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,207
    Thanks
    49
    Thanked 989 Times in 919 Posts
    "With .Range("L2:L" & LR)"

    "LR" is a variable?

    cheers, Paul

  9. The Following User Says Thank You to Paul T For This Useful Post:

    danny69 (2016-01-05)

  10. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Yes - sorry force of habit. You'd need to add:
    Code:
    Dim LR as long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    then use that in RG's code too.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. The Following User Says Thank You to rory For This Useful Post:

    danny69 (2016-01-05)

Posting Permissions

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