+ Reply to Thread
Results 1 to 3 of 3

To insert column and add formula

  1. #1
    Registered User
    Join Date
    01-22-2013
    Location
    Bangkok, Thailand
    MS-Off Ver
    Excel 2007
    Posts
    56

    To insert column and add formula

    Dear Expert,

    I would like to insert column between A column and B Column.

    Next add formula Trim in the new column in order to remove spacebar at material code.

    Next copy formula go down until last row data.

    at this time I can use macro only insert and need to how can add formula.

    Attached please find the Excel file.

    Remove spacebar .xlsx

    PS. macro use insert column.

    Sub Insrt_Mat()
    Dim Found As Range
    Dim LR As Long
    Set Found = Rows(1).Find(what:="Material", LookIn:=xlValues, lookat:=xlWhole)
    If Found Is Nothing Then Exit Sub
    LR = Cells(Rows.Count, Found.Column).End(xlUp).Row
    Found.Offset(, 1).EntireColumn.Insert
    Cells(1, Found.Column + 1).Value = ""
    'Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).FormulaR1C1 = "=Trim(A2)"
    End Sub

  2. #2
    Valued Forum Contributor Naveed Raza's Avatar
    Join Date
    11-04-2012
    Location
    India, Hyderabad
    MS-Off Ver
    Excel, Access 2007/2010
    Posts
    1,338

    Re: To insert column and add formula

    Hi

    try this

    Please Login or Register  to view this content.
    press alt+F8 and select the macro name and click on run
    Attached Files Attached Files
    Thanks - Naveed
    -----------------------------
    If the suggestion helps you, then Click * to Add Reputation
    To Attach File: Go Advanced>>Manage Attachments>>Add Files (In Top Right Corner)>>SelectFiles>>.........Locate Your File(s)>>Upload Files>>Done (In Bottom Right)
    1. Use [code] code tags [\code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    2. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

  3. #3
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,680

    Re: To insert column and add formula

    Please Login or Register  to view this content.
    This line will convert the formula cells as values

    Please Login or Register  to view this content.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

+ 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. [SOLVED] Insert Column with vLookup formula
    By MileHigh_PhD in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-25-2013, 03:03 AM
  2. Set of cells (in 1 column) won't add formula when I insert row
    By JPWRana in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 07-18-2013, 04:51 PM
  3. [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
  4. Macro to insert formula and copy down column
    By robertguy in forum Excel General
    Replies: 4
    Last Post: 03-01-2012, 12:03 PM
  5. Insert a column while not changing formula
    By kevironi in forum Excel General
    Replies: 4
    Last Post: 02-02-2011, 10:05 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