+ Reply to Thread
Results 1 to 6 of 6

Adding a new column at the end based on the first row

  1. #1
    Registered User
    Join Date
    11-14-2006
    Posts
    4

    Adding a new column at the end based on the first row

    Hi,

    I've been trying to get my head round this, tried doing find and replaces, IF and non of them really doing what I want to achieve.

    We've got a spreadsheet(Sample attached) if column anything in Column A = H then on that row in column J put in for example xxx

    If Column A = D then in Column E put in zzz

    I recorded a macro which was just me typing them in but the problem is that there may be 1000 lines and the macro I recorded was dependant on how many lines I actually did.

    If you can help that would be great.
    Attached Files Attached Files

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    3,970
    Hi Nicky,

    Just insert the following code in a standard VBA code module in your workbook:

    Please Login or Register  to view this content.
    Then select the worksheet which you want to test and run the above code.

    Hope this helps. Please let me know how you get on.

    Regards,

    Greg M

  3. #3
    Registered User
    Join Date
    11-14-2006
    Posts
    4
    Absolutly brilliant!

    Many thanks for this! I have one more question though and I hope someone can point me in the right direction.

    A customer sends us their order sheet by email everyday (We've got a program that takes the attachment and automatically saves it) it's always the same file name as well. We then put the macro in this email into the spreadsheet, save it as a .csv then our ordering system imports it in automatically for us.

    What I'm now trying to achieve is using windows task scheduler to open file.xls once a day and run the code in this thread, then get a new code which will save it as newfilename.csv

    How would I go about that and is it possible?

  4. #4
    Registered User
    Join Date
    11-14-2006
    Posts
    4
    Hi guys,

    I've been racking my head about this I'm hopeless!

    I've created a macro with the code in it that Greg kindly created for me and called it 'Module1' in a seperate spreadsheet called PERSONAL.xls.

    So when I set a windows task schedule to open the spreadsheet that comes to us called for example 'orders.xls' at 13:00.

    Then in the Personal.xls spreadsheet I've got this in it

    Private Sub Workbook_Open()
    Run "Module1"
    End Sub

    Which I hoped would run the script and change the orders.xls, then I have another module which will save it as a .csv file with a filename.

    But I can't get it to work properly if anyone could help it would be much appreciated many thanks.

    Nick

  5. #5
    Forum Contributor SOS's Avatar
    Join Date
    01-26-2004
    Location
    Glasgow, Scotland
    MS-Off Ver
    Excel 2003
    Posts
    327
    Hi nickyboyc,

    Unless you have renamed Greg's AddInformation Sub to "Module 1" then your Workbook_Open code won't work. If you have used Greg's code "as is" and inserted it INTO Module 1 of Personal.xls then your Workbook_Open code should read:

    Please Login or Register  to view this content.
    Hope this helps

    Seamus

  6. #6
    Registered User
    Join Date
    11-14-2006
    Posts
    4
    Ah right brilliant, now thats a start!

    Now all I have to do is get it to run when I open the orders spreadsheet.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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