# Delete column and insert column n formula automatically

1. ## 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

2. ## 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. Originally Posted by ikboy
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. ## 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``````

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

#### 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