+ Reply to Thread
Results 1 to 8 of 8

Assistance with Creating a Specific Macro using VBA code

  1. 12-08-2008, 04:47 AM #1
    Pbeer
    Pbeer is offline
    Registered User
    Join Date
    12-08-2008
    Location
    Zitterd
    Posts
    4

    Assistance with Creating a Specific Macro using VBA code

    Afternoon all,

    I was wondering if someone could help me generate a Macro to do the following:

    I have a sheet with the following characteristics.

    Column A, rows 8-15 contain headers
    Column A, rows 17-24 contain headers
    Column A, rows 26-40 contain headers
    Column B to CV, row 6 may or may not contain an "X"
    Column B to CV, rows 8-15 & 17-24 & 26-40 may or may not contain the various data


    I need a macro which does the following on the press of a button located somewhere on the sheet:
    • Generate a .TXT file in the folder C:\Test with the name "Test_YYYY_MM_DD_HH_MM_SS_Full.txt" which uses the system time and date to fill in the values
    • In the TXT file the following data should be created:
    • For Columns B to CV, row 6
    • if this cell contains the value "X" then copy/paste in the TXT File the following data:
    • for rows 8-15 of that particular column
  2. if Column XXX,Row#.value is not blanc
  • ColumnA,Row#.value directly followed by (no spacing) Column XXX,Row#.value directly followed by a return (or enter)
  • (whereas XXX is the value of the column which mathes the criteria which verifies if row 6 of that column contains an "X")
  • Add a blanc line (another return (or enter))
  • else proceed to row 17
  • for rows 17-24 of that particular column
  • if Column XXX,Row#.value is not blanc
  • ColumnA,Row#.value directly followed by (no spacing) Column XXX,Row#.value directly followed by a return (or enter)
  • (whereas XXX is the value of the column which mathes the criteria which verifies if row 6 of that column contains an "X")
  • Add a blanc line (another return (or enter))
  • else proceed to row 26
  • for rows 26-40 of that particular column
  • if Column XXX,Row#.value is not blanc
  • ColumnA,Row#.value directly followed by (no spacing) Column XXX,Row#.value directly followed by a return (or enter)
  • (whereas XXX is the value of the column which mathes the criteria which verifies if row 6 of that column contains an "X")
  • Add a blanc line (another return (or enter))
  • else do nothing
  • else proceed to the next column untill it reaches the last column (CV)

    • I hope I've explained this in a manner someone can understand and would VERY much appreciate any help which can be given on this.

    Many thanks in advance!
    Last edited by rylo; 12-17-2008 at 06:38 PM.
    Reply With QuoteRegister To Reply
  • 12-08-2008, 07:50 PM #2
    rylo
    rylo is offline
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Would help a lot if you added an example file with the first sheet showing your data, and the second sheet how you would expect the output to look.

    rylo
    Reply With QuoteRegister To Reply
  • 12-08-2008, 10:34 PM #3
    stanleydgromjr
    stanleydgromjr is offline
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387
    Pbeer,

    Welcome to the ExcelTip Board.

    Please attach your workbook - scroll down and see "Manage Attachments".


    Have a great day,
    Stan
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
    Reply With QuoteRegister To Reply
  • 12-09-2008, 05:00 AM #4
    Pbeer
    Pbeer is offline
    Registered User
    Join Date
    12-08-2008
    Location
    Zitterd
    Posts
    4

    Example Workbook attached

    As requested I attached an example workbook.

    The first tab contains the input data and the second tab shows what the output TXT file should contain.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  • 12-09-2008, 09:34 PM #5
    rylo
    rylo is offline
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    See how this goes.

    Please Login or Register  to view this content.
    rylo
    Reply With QuoteRegister To Reply
  • 12-12-2008, 03:47 AM #6
    Pbeer
    Pbeer is offline
    Registered User
    Join Date
    12-08-2008
    Location
    Zitterd
    Posts
    4
    Quote Originally Posted by rylo View Post
    Hi

    See how this goes.

    rylo
    Morning Rylo,

    First of all MANY thanks. It works almost perfect on the first go.
    I must say I am VERY impressed with the results !!!



    The result I got was a TXT file with the correct filename in the correct folder with the following contents

    EmployeeId:10000001
    EmployeeNumber:10000001
    Id:10000001-22210000-30000001
    FunctionId:30000001
    FunctionDescription:Software Tester
    OrganisationUnitId:22210000
    Primary:true
    ObjectType:Job
    BeginDate:1-12-2008
    DisplayName:Dhr. TU1 TeSt User1
    Id:10000001
    Initials:TU1
    Firstname:Test1
    MicrosectionNumber:123456
    MiddleName:TeSt
    MiddleNameSpouse:MidSpou1
    NameFormatType:1
    Surname:User1
    SurnameSpouse:SurSpou1
    Title:Dhr.
    Type:1
    ObjectType:User
    EmployeeId:10000002
    EmployeeNumber:10000002
    Id:10000002-21111300-30000002
    FunctionId:30000002
    FunctionDescription:WC Juffrouw
    OrganisationUnitId:21111300
    Primary:true
    ObjectType:Job
    BeginDate:1-12-2008
    DisplayName:Mevr. TU2 tteesstt SurSpou2 User2
    Id:10000002
    Initials:TU2
    Firstname:Test2
    MicrosectionNumber:654321
    MiddleName:tteesstt
    MiddleNameSpouse:MidSpou2
    NameFormatType:2
    Surname:User2
    SurnameSpouse:SurSpou2
    Title:Mevr.
    Type:1
    ObjectType:User


    What is missing here is:
    An additional blanc line (enter, carriage return or whatever you want to call it) after each "ObjectType:User" or "ObjectType:Job".

    Other then that, it works absolutely flawless !!!

    I don't know if you ever happen to come to the south of the Netherlands, but if you do send me an email and I'll happily treat you to a few beers
    Reply With QuoteRegister To Reply
  • 12-13-2008, 08:46 PM #7
    rylo
    rylo is offline
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591
    Hi

    Here goes.

    Please Login or Register  to view this content.
    rylo
    Reply With QuoteRegister To Reply
  • 12-17-2008, 05:35 AM #8
    Pbeer
    Pbeer is offline
    Registered User
    Join Date
    12-08-2008
    Location
    Zitterd
    Posts
    4

    Talking All Hail Rylo ;)

    Quote Originally Posted by rylo View Post
    Hi

    Here goes.

    rylo
    Absolutely perfect

    Many, MANY thanks mate!
    Reply With QuoteRegister To Reply
    • + Reply to Thread
    « Previous Thread | Next 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

    Forum Rules


    Search Engine Friendly URLs by vBSEO 3.6.0 RC 1