+ Reply to Thread
Results 1 to 21 of 21

Macro to convert WBS single digit numbers to double digit numbers

  1. #1
    Registered User
    Join Date
    01-08-2014
    Location
    Bath, UK
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    84

    Macro to convert WBS single digit numbers to double digit numbers

    I have a WBS structure for example M1.1.1.1.1.1.1.1 and am trying to convert the single digit numbers to double digit numbers (e.g. change it to M01.01.01.01.01.01.01.01) so it sorts properly (when the rest of the WBS hierarchy is populated. If the number is already two digits (e.g. .13. it should be left as .13. and should not have a leading zero added).

    I have created a simple bit of code to replace .1. with .01. to change it to M1.01.01.01.01.01.01.1

    I’ve also created another bit of code which changes the M1 to M01

    However, I am struggling with the last single digit (of a text string of variable length) – in the example above, it is “.1” (rather than “.1.”) which I need changing to “.01”.

    The code I already have is:
    Please Login or Register  to view this content.
    (if you also think there’s a better way to write this existing code, feel free to suggest it)

    The code needs to check whether the penultimate character is a full stop/decimal point/period and whether the last character is a number, then insert a leading zero. (if the penultimate character is not a full stop/decimal point/period, the final number is presumably already two digits and can remain). E.g. I am trying to change the final .1 (however long the string is) to .01

    If I was using a formula, this would be straightforward, but I do not know how to manipulate the string within a macro.

    Can you help, please?

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

    Re: Macro to convert WBS single digit numbers to double digit numbers

    Hi there,

    See if the following code does what you need:

    Please Login or Register  to view this content.
    The highlighted values may be altered to suit your own requirements.


    Hope this helps - please let me know how you get on.

    Regards,

    Greg M

  3. #3
    Registered User
    Join Date
    01-08-2014
    Location
    Bath, UK
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    84

    Re: Macro to convert WBS single digit numbers to double digit numbers

    Thanks Greg.

    It seems to work a little inconsistently - occasionally, it looks to run OK, other times it copies the result from A1 into the whole A1-A5 range, if it is run again, it then removes characters/modifies the string even when the changes are not necessary (i.e. if it has already converted the 1 digit numbers to 2 digits, it shouldn't do anything else if the user re-runs the macro).
    Would you mind looking at it again, please?

    Also, you've proposed a defined range of A1;A5 - can this be changed to a dynamic range to search all the way down column A.

    Thanks

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,106

    Re: Macro to convert WBS single digit numbers to double digit numbers

    Example Function.
    If data is in col.a
    Please Login or Register  to view this content.
    Last edited by jindon; 06-04-2018 at 09:21 AM.

  5. #5
    Registered User
    Join Date
    01-08-2014
    Location
    Bath, UK
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    84

    Re: Macro to convert WBS single digit numbers to double digit numbers

    Thanks @jindon - that seems to work perfectly

    Greg - happy for you to leave it, or happy to have a look at an amendment...

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,106

    Re: Macro to convert WBS single digit numbers to double digit numbers

    You are welcome and thanks for the rep.

  7. #7
    Registered User
    Join Date
    01-08-2014
    Location
    Bath, UK
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    84

    Re: Macro to convert WBS single digit numbers to double digit numbers

    Just a quick query.... (well, hopefully it's relatively quick..)

    Although the user should enter the WBS code with a single character and without a space (e.g. M1.1.1.1, etc), some might enter it as "WBS 1.1.1.1" (either with or without a space) and some might actually just enter it as "1.1.1.1" without any letters...

    Is it possible to add the code to cater for these two scenarios, please?

  8. #8
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,458

    Re: Macro to convert WBS single digit numbers to double digit numbers

    Hi again,

    Sorry - definitely my fault

    I had spotted the error and was working on correcting it while you were posting.

    Try the following version - it will test/change all of the cells in the UsedRange of Column A:

    Please Login or Register  to view this content.
    The highlighted values may be changed to suit your own requirements.


    Hope this helps - as before, please let me know how you get on, and thank you for the (undeserved!) Reputation increase.

    Regards,

    Greg M

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,106

    Re: Macro to convert WBS single digit numbers to double digit numbers

    Just change the pattern to
    Please Login or Register  to view this content.
    If possibly space(s) at both sides then
    Please Login or Register  to view this content.
    Last edited by jindon; 06-04-2018 at 09:56 AM.

  10. #10
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,458

    Re: Macro to convert WBS single digit numbers to double digit numbers

    Hi again,

    See if the attached workbook does what you requested in your last post.

    The workbook contains code to generate numbers with various prefixes ("M", "M ", " M", " M ", "WBS", "WBS ", " WBS", " WBS " and blank) so that you can see the effect of the clean-up code.

    Hope this helps.

    Regards,

    Greg M
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    01-08-2014
    Location
    Bath, UK
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    84

    Re: Macro to convert WBS single digit numbers to double digit numbers

    Thanks @jindon...

    I've tried going through some of the different scenarios and, through trial and error, look to have found the code for a couple of the scenarios, but they only seem to work (the way I have amended them) when the code is run separately - i.e. if I know the text is in one format, I can select the specific code. Is it possible to combine the code so it can cope with each scenario (as I, or the user, will not know which format the text is in) - sorry if it's relatively basic, but I've not seen code in a function before

    The most likely scenarios I can think of are:
    M1.1.1.1.1.1 (without a space)
    M 1.1.1.1.1.1 (with a space)
    WBS1.1.1.1.1.1 (without a space)
    WBS 1.1.1.1.1.1 (with a space)
    1.1.1.1.1.1 (just numbers, no text)

  12. #12
    Registered User
    Join Date
    01-08-2014
    Location
    Bath, UK
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    84

    Re: Macro to convert WBS single digit numbers to double digit numbers

    @Greg - that spreadsheet definitely deserves the "rep"! it looks to do the job!

    Can I just ask about one (hopefully relatively minor) tweak - you've forced the resulting code to be M01.02.03.04.05 etc (i.e. M with no space), regardless of how it was actually entered, which is based on my initial post... If the user wants the text element to remain flexible, but the numbers to be modified as you have correctly done, which code would need to change..?
    The most likely scenarios I can think of are:
    M1.1.1.1.1.1 (without a space)
    M 1.1.1.1.1.1 (with a space)
    WBS1.1.1.1.1.1 (without a space)
    WBS 1.1.1.1.1.1 (with a space)
    1.1.1.1.1.1 (just numbers, no text)
    i.e. leave the text as the user has entered it, but do your funky stuff on the numbers

    Thanks

  13. #13
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,458

    Re: Macro to convert WBS single digit numbers to double digit numbers

    Hi again,

    The attached workbook retains whatever prefix was entered by the User, and inserts the appropriate zeroes into the text string.

    The simplest way for me to implement this was to use a dummy prefix ("@" instead of "M") at the start of the process and then (at the end of the process) to replace the dummy prefix with whatever prefix had been entered by the User originally.

    I've listed the code here in case other viewers don't wish to open the workbook:

    Please Login or Register  to view this content.
    Hope this helps - as always, please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    01-08-2014
    Location
    Bath, UK
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    84

    Re: Macro to convert WBS single digit numbers to double digit numbers

    Hi Greg,

    Thanks again for your help on this…

    The macro works great on WBS1.1.1, WBS1.1.2, WBS1.1.3, etc. and various forms of the prefix.

    However, the macro assumes that WBS 1.1, WBS 1.2, WBS 1.3 etc. are not valid formats, yet these are the highest levels that could actually use – can it be amended to allow/include this level? The macro converts the number correctly, so I do not see why the “error message” should be triggered…?
    I’m happy for the macro to ignore WBS 1, WBS 2, WBS 3 etc. as these should not be used in the spreadsheet as they relate to the project as a whole.

    I wondered whether when the “incorrect format” message has been triggered, can the macro show the progress in the list? When I’ve been testing it, for example, I’ve had about 40 or so “incorrect format” messages (which I understand – they were WBS 1.1, WBS 1.2, WBS 1.3 etc), but it would be nice (or reassuring) to see the selected cell steadily moving down the column. I appreciate that in a live situation there shouldn’t be any/many “errors”, but I think the average user would be reassured to see the cursor moving down the column (rather than thinking the “error message” is more serious. I usually use Application.Screenupdating=False, so am happy if the default is that progress is not actually shown, but if there is an error, I think it would be nice the macro showed where this had been. On reflection, it’s probably better not to highlight the cell (in case the user has coloured the cells) – would it be possible to display the WBS which triggered the “incorrect format” – e.g. “Incorrect format – WBS 1 1 1 1 1” (e.g. the user had not used any dots in the WBS).
    I’ve amended the “incorrect format” code to the following (which seems to resolve this issue):
    Please Login or Register  to view this content.

    The spreadsheet searches down the specified column (e.g. all of column A) – can it be changed to start at, say, row 5 and look down the used range?

    Finally, I’ve forgotten to mention (or perhaps assumed it would be easy for me to change or just duplicate the macro) – we have the generic WBS structure, but we also have (confusingly) another hierarchy which follows a similar numbering structure… Can the macro be amended to run on two columns? (say columns A & D – or A5 down and D5 down).

    Thanks,
    Graham

  15. #15
    Registered User
    Join Date
    01-08-2014
    Location
    Bath, UK
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    84

    Re: Macro to convert WBS single digit numbers to double digit numbers

    Hi Greg,

    Changing the macro to look down additional columns looks deceptively straightforward - I changed one line of code to:
    Please Login or Register  to view this content.
    Unfortunately, the code seems to crash if there is a blank cell - either at the top of the column (if the data actually starts at, say, row 5) or if the user has omitted the WBS (and completed other columns in the spreadsheet, but not shown in the example). Can you find a way to skip/handle the blank cells?

    thanks,
    Graham

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,106

    Re: Macro to convert WBS single digit numbers to double digit numbers

    Quote Originally Posted by LeanAccountant View Post
    Thanks @jindon...

    I've tried going through some of the different scenarios and, through trial and error, look to have found the code for a couple of the scenarios, but they only seem to work (the way I have amended them) when the code is run separately - i.e. if I know the text is in one format, I can select the specific code. Is it possible to combine the code so it can cope with each scenario (as I, or the user, will not know which format the text is in) - sorry if it's relatively basic, but I've not seen code in a function before

    The most likely scenarios I can think of are:
    M1.1.1.1.1.1 (without a space)
    M 1.1.1.1.1.1 (with a space)
    WBS1.1.1.1.1.1 (without a space)
    WBS 1.1.1.1.1.1 (with a space)
    1.1.1.1.1.1 (just numbers, no text)
    If you can upload a workbook with various pattern with before/after as well as valid/invalid, it will be clear.
    This is only my guess.
    Please Login or Register  to view this content.

  17. #17
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,458

    Re: Macro to convert WBS single digit numbers to double digit numbers

    Hi again,

    The following code in the attached workbook should do what you requested in your last posts:

    Please Login or Register  to view this content.
    The highlighted values can be altered to suit the range of cells you wish to process.

    Cells which contain no value are ignored.

    Cells whose values have formatting errors are selected as they are identified.


    Hope this helps - as always, please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    01-08-2014
    Location
    Bath, UK
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    84

    Re: Macro to convert WBS single digit numbers to double digit numbers

    Hi Greg,

    Thanks again for the updates...

    If it identifies that it’s in the “incorrect format”, it correctly selects the cell, so the progress can be seen - thank you!

    Unfortunately, though, it is still identifying WBS 1.1, WBS 1.2, WBS 1.3, etc. as being errors, but these are not errors, they are perfectly valid WBS codes.
    In the previous version, when it identified one of these (e.g. WBS 1.1, WBS 1.2, WBS 1.3) as being incorrect, it went ahead and amended the code (e.g. WBS 01.01, WBS 01.02, WBS 01.03) – which is the outcome I sought (ideally without the error message).
    Now, though, having identified it as being incorrect, it actually ignores it (and leaves them as, for example, WBS 1.1, WBS 1.2, WBS 1.3), which is wrong.
    Can it be changed to accept WBS 1.1, WBS 1.2, WBS 1.3, etc. as being valid WBS and change them to WBS 01.01, WBS 01.02, WBS 01.03 as per the other valid WBS codes?

    Finally, if it can’t find a full stop (a proper “incorrect format”, it crashes – can it just indicate the cell (as above, to show the progress) and skip to the next one?

    Thanks,
    Graham

  19. #19
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,458

    Re: Macro to convert WBS single digit numbers to double digit numbers

    Hi again Graham,

    Ok, the version in the attached workbook will indicate values which contain no full stop, but will take no corrective action and will continue to process the remaining values.


    Unfortunately, though, it is still identifying WBS 1.1, WBS 1.2, WBS 1.3, etc. as being errors, but these are not errors, they are perfectly valid WBS codes.

    In the previous version, when it identified one of these (e.g. WBS 1.1, WBS 1.2, WBS 1.3) as being incorrect, it went ahead and amended the code as being incorrect, it went ahead and amended the code (e.g. WBS 01.01, WBS 01.02, WBS 01.03) which is the outcome I sought (ideally without the error message).

    Maybe I'm missing something here, but Codes which begin with "WBS 1." and "WBS 01." etc. are being processed correctly and are not indicating errors at this end. To demonstrate this, the attached workbook contains a button labelled "Generate Numbers - WBS Prefix" which will generate a series of numbers, all of which begin with the prefix "WBS ". Clicking on the "Apply WBS Structure" button processes these numbers without generating any error messages.

    Take a look at the attached workbook and see what you think.

    Regards,

    Greg M
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    01-08-2014
    Location
    Bath, UK
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    84

    Re: Macro to convert WBS single digit numbers to double digit numbers

    Hi Greg,

    That seems a little puzzling...

    I opened the latest version of your file, ran the code generator and it worked fine (as, I think, it always has done).

    However, when I entered the following WBS codes, it gives the "incorrectly formatted" error message and moves on to the next one without changing it.

    WBS 1.1
    WBS 1.2
    WBS 1.3
    WBS 1.4
    WBS 1.5
    WBS 1.6
    WBS 1.7
    WBS 1.8
    WBS 1.9

    These are perfectly valid WBS codes - it shouldn't flag them as errors and it should change them to WBS 01.01, WBS 01.02, WBS 01.03 etc.

    In an earlier version you sent, it flagged them as errors, but it did actually change them (correctly) - although the "error message" was generated incorrectly, the macro did change the WBS codes correctly.

    I tried attaching a screenshot, but I think our IT permissions prevent the "attach file" pop up opening

    If it helps, the full list I used to test the macro is:

    Column A
    WBS 1.1
    WBS 1.2
    WBS 1.3
    WBS 1.4
    WBS 1.5
    WBS 1.6
    WBS 1.7
    WBS 1.8
    WBS 1.9
    WBS 1.10
    WBS 1.11
    WBS 1.12
    WBS 1.13
    WBS 1.14
    WBS 1.15
    WBS 1.16
    WBS 1.17
    WBS 1.18
    WBS 1.19
    WBS 1.20
    WBS 1.21
    WBS 1.22
    WBS 1.23
    WBS 1.24
    WBS 1.25

    Column B (I overtyped column A of WBS numbers your macro generated and overtyped part of column B)
    WBS 1.1
    WBS 2.1
    WBS 3.1
    WBS 4.1
    WBS 5.1
    WBS 6.1
    WBS 7.1
    WBS 8.1
    WBS 9.1

    Unfortunately the macro indicates all of these as "incorrect formats" and refuses to change them to WBS 01.01, WBS 01.02, etc

    thanks,
    Graham

  21. #21
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,458

    Re: Macro to convert WBS single digit numbers to double digit numbers

    Hi again Graham,

    Sorry for the delay in replying.

    Well, the problem had nothing to do with the use of a WBS prefix, but rather with the fact that the codes you indicated contain only a single decimal point!

    With luck, the attached workbook should do what you need. I've also tweaked the code to cover the situation where the numbers to be converted are e.g. 1.2 (i.e. without any prefix). The previous code correctly converted the value to 01.02, but when that value was entered into a cell, Excel interpreted it as a numeric value and dropped the leading zero. The current version of the code will prefix any numeric value with a single quote thereby converting the value to a string and ensuring that any leading zero is retained.

    The code used is as follows:

    Please Login or Register  to view this content.

    Hope this helps - as always, please let me know how you get on.

    Regards,

    Greg
    Attached Files Attached Files

+ 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. Bank Recon - How to convert 8 digit formatted check numbers to 6 digit
    By Quisp in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-21-2017, 09:55 PM
  2. IF(ISNUMBER(SEARCH single and double digit numbers
    By marie branigan in forum Excel General
    Replies: 5
    Last Post: 09-16-2015, 06:08 PM
  3. Macro to identify 3-digit and 4-digit numbers as valid dates
    By gojakie in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-16-2015, 08:16 AM
  4. [SOLVED] Countif & Sumif with single and double digit numbers mixed in same column.
    By hoventim in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 03-15-2015, 03:19 PM
  5. [SOLVED] LOTTERY FILTER#4, Find if 1 Digit Sum of 2 Digit or 3 Digit, Single Cell w/ dash
    By david gonzalez in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-08-2014, 12:57 AM
  6. Extract 4 digit common numbers from 5 digit numbers
    By ameque in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-24-2013, 01:09 AM
  7. [SOLVED] macro to extract 9 digit numbers as well as alpha numberic 9 digit numbers from txt file
    By Raju Radhakrishnan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-13-2012, 10:15 AM

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