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

    Convert Formula to Macro

    Hi,

    I need to convert this formula to Macro which will ease my work, no more need to drag it.
    Please some help me to convert this formula into a Macro.

    Code:
    Formula in column J
    =IF(C2=10,"1010",IF(C2=11,"1111","1414"))
    
    
    Formula in column K
    =RIGHT(CONCATENATE("00000000",D2),8)
    
    
    Formula in column L
    =CONCATENATE(I2,J2,K2)
    Thanks in advance.

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,196
    Thanks
    48
    Thanked 986 Times in 916 Posts
    Do you want the values in J, K & L or do you just want the result in K?

    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
    Do you want the values in J, K & L or do you just want the result in K?

    cheers, Paul
    Thanks for reply.
    Yes first to have the values in J, K and then in L.

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,196
    Thanks
    48
    Thanked 986 Times in 916 Posts
    Personally I'd leave the 3 formulas as they are. Having a macro to add values to 3 cells seems a lot of work for not much gain.
    Rather than dragging the formula you can select the cell and then double click on the bottom right cell corner to auto-fill the cells below, assuming you have data in the preceding column.

    cheers, Paul

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,639
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Danny,

    Paul is probably right but if your situation warrants it, try this:
    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)"
    'COMMENT OUT OR REMOVE THE NEXT LINE IF YOU DO NOT WANT TO FILL THE COLUMNS
        .Range("J2:L2").AutoFill Destination:=Range("J2:L15"), Type:=xlFillDefault
    End With
    End Sub
    DANNY2.png

    HTH,
    Maud
    Last edited by Maudibe; 2015-08-04 at 06:30. Reason: added image

  6. The Following User Says Thank You to Maudibe For This Useful Post:

    danny69 (2015-08-04)

Posting Permissions

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