+ Reply to Thread
Results 1 to 3 of 3

To Find and Insert column and add Formula and copy formula down

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

    To Find and Insert column and add Formula and copy formula down

    Dear Expert Excel person,

    I need you help me to find some solution, I try to write a macro as below :

    Sub Insrt_MatType()
    Dim Found As Range
    Dim LR As Long
    Set Found = Rows(1).Find(what:="Material description", 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 = "Material type"
    Range(Cells(2, Found.Column + 1), Cells(LR, Found.Column + 1)).Formula = "=IF(ISNUMBER(FIND("S",A2)),"Sample",IF(ISNUMBER(FIND("Z",A2)),"Sample",IF(ISNUMBER(FIND("T",A2)),"Tester",IF(ISNUMBER(FIND("Y",A2)),"Tester","FG"))))"
    End Sub

    I need to insert column and add formula in the new column and copy formula down, but I cannot add formula in this macro.

    i do not how can solve this one, i need you advice me this case.

    And I have attached Excel file, can you see in Tab sheet Raw data.


    Best regards,
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-22-2013
    Location
    London, United Kingdom
    MS-Off Ver
    Office 365
    Posts
    1,217

    Re: To Find and Insert column and add Formula and copy formula down

    Hi,

    After testing the code in the workbook you provided, I saw that the raw data header "Material description" had trailing spaces, why you can use the find function xlpart instead of xlwhole. In addition, the numberformatting of the raw data cells needs reformatting for formulas to work.

    I did also tweak your code a little bit as I rewrote it:
    Please Login or Register  to view this content.
    Hope it works.

    Cheers,
    berlan
    Last edited by berlan; 07-27-2014 at 09:34 PM. Reason: EDIT after testing the workbook provided

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

    Re: To Find and Insert column and add Formula and copy formula down

    Thank you, this one can help me

+ 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] VBA macro to add header text, insert & copy-down formula, move column
    By shootstill in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-24-2014, 05:44 AM
  2. Vba - find next empty column, insert column, copy paste values & copy paste formulas
    By DoodlesMama in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-20-2012, 12:43 PM
  3. [SOLVED] Formula to find header and insert in new column
    By dbracken1 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-03-2012, 03:50 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. find value, insert row,copy formula, enter data
    By herukuncahyono in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-22-2010, 11:39 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