+ Reply to Thread
Results 1 to 4 of 4

Delete column and insert column n formula automatically

  1. #1
    Registered User
    Join Date
    01-25-2016
    Location
    asia
    MS-Off Ver
    2010
    Posts
    2

    Delete column and insert column n formula automatically

    Hi All Excel master,
    I have file excel AB10 it contain raw data need to be edit and put in new workbook AB. Raw data contain a few unused column so i need to delete it and then i also need to insert some columns with excel formula. Here i Attach the file,
    1. i need to delete P column.
    2. i need to insert 2 column after N column with formula in O column = (N10-O$8)/30 and P column =IF(O10<=1,"(0 - 1 bulan)",IF(O10<=3,"(>1 - 3 bulan)",IF(O10<=6,"(>3 - 6 bulan)",IF(O10<=12,"(>6 - 12 bulan)",IF(O10<=24,"(>1 - 2 tahun)",IF(O10<=36,"(>2 - 3 tahun)",IF(O10<=48,"(>3 - 4 tahun)",IF(O10<=60,"(>4 - 5 tahun)"))))))))
    please kindly help me to solve this problem using macro. Thanks in advance
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    12-22-2015
    Location
    HK
    MS-Off Ver
    2010
    Posts
    489

    Re: Delete column and insert column n formula automatically

    Try this:

    HTML Code: 
    Sub zz()
    Columns("P").Delete
    Columns("o:p").Insert
    Cells(10, "o").Formula = "=(N10-O$8)/30"
    Cells(10, "p").Formula = "=IF(O10<=1,""(0 - 1 bulan)"",IF(O10<=3,""(>1 - 3 bulan)"",IF(O10<=6,""(>3 - 6 bulan)"",IF(O10<=12,""(>6 - 12 bulan)"",IF(O10<=24,""(>1 - 2 tahun)"",IF(O10<=36,""(>2 - 3 tahun)"",IF(O10<=48,""(>3 - 4 tahun)"",IF(O10<=60,""(>4 - 5 tahun)""))))))))"
    End Sub
    or

    HTML Code: 
    Sub zz()
    Columns("P").Delete
    Columns("o:p").Insert
    Cells(10, "o").Formula = "=(N10-O$8)/30"
    Cells(10, "p").Formula = "=LOOKUP(O10,{1,3,6,12,24,36,48,60},{""(0 - 1 Bulan)"",""(>1 - 3 Bulan)"",""(>3 - 6 Bulan)"",""(>6 - 12 Bulan)"",""(>1 - 2 Tahun)"",""(>2 - 3 Tahun)"",""(>3 - 4 Tahun)"",""(>4 - 5 Tahun)""})"
    End Sub

  3. #3
    Registered User
    Join Date
    01-25-2016
    Location
    asia
    MS-Off Ver
    2010
    Posts
    2
    Quote Originally Posted by ikboy View Post
    Try this:

    HTML Code: 
    Sub zz()
    Columns("P").Delete
    Columns("o:p").Insert
    Cells(10, "o").Formula = "=(N10-O$8)/30"
    Cells(10, "p").Formula = "=IF(O10<=1,""(0 - 1 bulan)"",IF(O10<=3,""(>1 - 3 bulan)"",IF(O10<=6,""(>3 - 6 bulan)"",IF(O10<=12,""(>6 - 12 bulan)"",IF(O10<=24,""(>1 - 2 tahun)"",IF(O10<=36,""(>2 - 3 tahun)"",IF(O10<=48,""(>3 - 4 tahun)"",IF(O10<=60,""(>4 - 5 tahun)""))))))))"
    End Sub
    or

    HTML Code: 
    Sub zz()
    Columns("P").Delete
    Columns("o:p").Insert
    Cells(10, "o").Formula = "=(N10-O$8)/30"
    Cells(10, "p").Formula = "=LOOKUP(O10,{1,3,6,12,24,36,48,60},{""(0 - 1 Bulan)"",""(>1 - 3 Bulan)"",""(>3 - 6 Bulan)"",""(>6 - 12 Bulan)"",""(>1 - 2 Tahun)"",""(>2 - 3 Tahun)"",""(>3 - 4 Tahun)"",""(>4 - 5 Tahun)""})"
    End Sub
    hey thanks for the code,i havent try it yet but 1 more question i want to make every cell in o and p filled until they found "total row" as seen in excel file,do you knpw the code as well? thanks b4

  4. #4
    Valued Forum Contributor
    Join Date
    12-22-2015
    Location
    HK
    MS-Off Ver
    2010
    Posts
    489

    Re: Delete column and insert column n formula automatically

    Try this:

    HTML Code: 
    Sub zz()
    Columns("P").Delete
    Columns("o:p").Insert
    Cells(10, "o").Formula = "=(N10-O$8)/30"
    Cells(10, "p").Formula = "=LOOKUP(O10,{1,3,6,12,24,36,48,60},{""(0 - 1 Bulan)"",""(>1 - 3 Bulan)"",""(>3 - 6 Bulan)"",""(>6 - 12 Bulan)"",""(>1 - 2 Tahun)"",""(>2 - 3 Tahun)"",""(>3 - 4 Tahun)"",""(>4 - 5 Tahun)""})"
    Range(Cells(10, "o"), Cells(Cells(10, "n").End(4).Row, "p")).FillDown
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Automatically change the range of the Macro Formula when i insert column or row ??
    By jasond1992 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 12-08-2015, 01:57 AM
  2. Replies: 5
    Last Post: 03-18-2015, 10:14 AM
  3. Automatically delete column once the date in that column has expired
    By TAN MICCOLI in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-30-2014, 09:26 PM
  4. Replies: 1
    Last Post: 08-24-2012, 03:18 PM
  5. Run Macro Automatically When delete row , changes cell,delete column
    By elevisse in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-27-2012, 07:42 AM
  6. [SOLVED] Formula to Insert the No 1 in column b when a number is detected in Column A
    By leinster93 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-29-2012, 11:21 AM
  7. Macro to delete first row and insert column after a certain number of column
    By arnab0711 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-26-2011, 08:36 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1