We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Insert Columns and Formulas

lkirke
lkirke asked
on
Medium Priority
273 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

Comment
Watch Question

Commented:
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
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

Author

Commented:
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
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Thank you once again Experts. :)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.