?
Solved

Insert Columns and Formulas

Posted on 2011-04-28
5
Medium Priority
?
262 Views
Last Modified: 2012-05-11
Hello Experts,

Have a number of different worksheets that contain data. I would like to insert 2 columns within each worksheet, then add a couple of formulas.

The following would be the procedure:
1) For all the sheets indicated below, insert two columns at Column B.
2) Again for all the sheets, in the first column, insert a formula, starting from row 11 until there is no more data in column A, the following formula: INT(A11)
3) Lastly, for all the sheets, in the second column, insert a formula, starting from row 11 until there is no more data in column A, the following formula: =MOD(A11,1)

Regards

LK
Sheets("AREA_1")
Sheets("AREA_2")
Sheets("COUNTRY_1")
Sheets("COUNTRY_2")
Sheets("COUNTRY_3")
Sheets("CITY_1")
Sheets("CITY_2")
Sheets("CITY_3")

Open in new window

0
Comment
Question by:lkirke
  • 2
  • 2
5 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 35481516
Please take a look at the code below. I attached a sample file.

jppinto
Sub YourMacro()
Dim ws As Worksheet
Dim lstRow As Long
Dim x As Long
    Sheets(Array("AREA_1", "AREA_2", "COUNTRY_1", "COUNTRY_2", "COUNTRY_3", "CITY_1", _
        "CITY_2", "CITY_3")).Select
    Sheets("AREA_1").Activate

    Columns("B:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    For Each ws In Sheets(Array("AREA_1", "AREA_2", "COUNTRY_1", "COUNTRY_2", "COUNTRY_3", "CITY_1", _
        "CITY_2", "CITY_3"))
        ws.Select
        lstRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        For x = 11 To lstRow
            ws.Cells(x, 2).FormulaR1C1 = "=INT(RC[-1])"
            ws.Cells(x, 3).FormulaR1C1 = "=MOD(RC[-2],1)"
        Next x
    Next ws
    
End Sub

Open in new window

InsertColumnsMacro.xlsm
0
 
LVL 1

Expert Comment

by:smartchaps
ID: 35481961
I think the modified code below will solve the problem and will give desired results:

 
Sub YourMacro()
Dim ws As Worksheet
Dim lstRow As Long
Dim x As Long
    
    For Each ws In Sheets(Array("AREA_1", "AREA_2", "COUNTRY_1", "COUNTRY_2", "COUNTRY_3", "CITY_1", _
        "CITY_2", "CITY_3"))
        ws.Select
        Columns("B:C").Select
        Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        lstRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        For x = 11 To lstRow
            ws.Cells(x, 2).FormulaR1C1 = "=INT(RC[-1])"
            ws.Cells(x, 3).FormulaR1C1 = "=MOD(RC[-2],1)"
        Next x
    Next ws
    
End Sub

Open in new window

0
 

Author Comment

by:lkirke
ID: 35488385
Great Experts. Both solutions work. :)

Just a final question:
How do I format the Column B formulas to dd/mm/yyyy date format?
How do I format the Column C formulas to hh:mm time format?

Regards

LK
0
 
LVL 33

Accepted Solution

by:
jppinto earned 2000 total points
ID: 35489403
Just added the aditional code to do what you wanted...

jppinto
Sub YourMacro()
Dim ws As Worksheet
Dim lstRow As Long
Dim x As Long
    Sheets(Array("AREA_1", "AREA_2", "COUNTRY_1", "COUNTRY_2", "COUNTRY_3", "CITY_1", _
        "CITY_2", "CITY_3")).Select
    Sheets("AREA_1").Activate

    Columns("B:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    For Each ws In Sheets(Array("AREA_1", "AREA_2", "COUNTRY_1", "COUNTRY_2", "COUNTRY_3", "CITY_1", _
        "CITY_2", "CITY_3"))
        ws.Select
        lstRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        For x = 11 To lstRow
            ws.Cells(x, 2).FormulaR1C1 = "=INT(RC[-1])"
            ws.Cells(x, 3).FormulaR1C1 = "=MOD(RC[-2],1)"
        Next x
        Columns("B:B").Select
        Selection.NumberFormat = "dd/mm/yyyy;@"
        Columns("C:C").Select
        Selection.NumberFormat = "h:mm;@"
    Next ws
    
End Sub

Open in new window

0
 

Author Closing Comment

by:lkirke
ID: 35509914
Thank you once again Experts. :)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question