+ Reply to Thread
Results 1 to 3 of 3

macro required to separate mobile phone no. from landline phone no.s

  1. #1
    Registered User
    Join Date
    09-06-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    22

    macro required to separate mobile phone no. from landline phone no.s

    Second time lucky, i hope..... spent best part of an hour only to be greeted by a message telling me to log in again and to find the post had been deleted.. arrgghh!!!

    In a nutshell at the minute I am working with my colleague trying to implement a new computer system at work. The original system was DOS based and used two separate databases, Accs and TSHTCUST. the new system (using MySQL) requires that both databases are merged, and I have been left with the unenviable task of trying to get the two sets of data into one set of boxes.

    The data collected in both sets is as follows:

    Customer Code:
    Customer:
    Address 1:
    Address 2:
    Address 3:
    Address 4:
    Address 5:
    Post Code:
    Invoice Code:
    Website:
    E Trader Y / N:
    Default VAT Code:
    Pro FormaY / N:
    Pro Forma Reason:
    Aerospace Y / N:
    Currency ID:
    HT Flag:
    HT Contact:
    HT Phone:
    HT Fax:
    HT Contact2:
    HT Phone2:
    HT Contact3:
    HT Phone3:
    HT Contact4:
    HT Phone4:
    TS Flag:
    TS Contact:
    TS Phone:
    TS Fax:
    TS Contact2:
    TS Phone2:
    TS Contact3:
    TS Phone3:
    TS Contact4:
    TS Phone4:
    Email
    Email 2
    Email 3
    Status:

    The original data has been exported into an excel workbook and i have created a dynamic look up using the following code with "B4" being the drop down.

    Please Login or Register  to view this content.
    This checks both sets of data and then displays them side by side so as to enable me to create TWO compound datasets to include the following:

    DATASET 1
    Customer ID:
    Customer:
    Address 1:
    Address 2:
    Address 3:
    Address 4:
    Address 5:
    Post Code:
    Aerospace Y / N:
    Web:
    Currency ID:
    Default VAT Code:
    E Trader Y / N:
    Invoice to Code:
    Invoice to E-mail Address:
    Pro Forma Y / N:

    The reason behind this is because their are multiple occasions where the data has been entered into the wrong box originally.

    The table below would obviously be replicated as per contact.

    DATASET 2
    Customer ID:
    Name:
    Telephone No:
    Mobile No:
    Fax No:
    Email Address:
    System ID:

    In order to get the information into Dataset 1 I have used various if commands i.e:
    Please Login or Register  to view this content.
    I then created a macro (see below) that will copy the data, paste special (as values), recopy, transpose and paste into another sheet in the next available row.
    Please Login or Register  to view this content.
    i am now trying to find a piece of code that will enable me to separate Mobile/Cell phone numbers from Land line phone numbers

    i have used the following function to clean all the numbers to make them read as uk:

    Please Login or Register  to view this content.
    due to the nature of the information in the databases it is not possible for me to supply a copy of the work book and putting in dummy info would be a complete nightmare.

    Any help would be greatly appreciated.

    Thanks

    Jez
    Last edited by Poseidons-Palace; 10-03-2012 at 02:02 PM.

  2. #2
    Registered User
    Join Date
    09-06-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: macro required to separate mobile phone no. from landline phone no.s

    I have now recorded the following macro:

    Please Login or Register  to view this content.
    I am now even more confused because i have the following code in cell I28

    Please Login or Register  to view this content.
    But after running the above macro the following code is then in the cell

    Please Login or Register  to view this content.
    Is there something i am missing in the macro????

    cheers

    Jez

  3. #3
    Registered User
    Join Date
    09-06-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: macro required to separate mobile phone no. from landline phone no.s

    I have now recorded the following macro:

    Please Login or Register  to view this content.
    I am now even more confused because i have the following code in cell I28

    Please Login or Register  to view this content.
    But after running the above macro the following code is then in the cell

    Please Login or Register  to view this content.
    Is there something i am missing in the macro????

    cheers

    Jez

+ 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