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?

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

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

Thanks @jindon - that seems to work perfectly

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

You are welcome and thanks for the rep.

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?

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

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

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)

@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

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

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

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

Originally Posted by LeanAccountant

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.

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

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

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

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

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