Using wildcards in VBA to check string contents in If...Then statement

Hi, Y'all,

New to the forum and kind of a novice. I have about 70-some-odd workbooks with a column that has a RouteID like so:

BLU1
BLU2
...
BLU39
GRE1
GRE2
...
GRE40
ORA1
...etc

Need to renumber to BLU01, BLU02, GRE01, GRE02, etc, cuz as y'all know, when you sort with numbers like I have, it comes out BLU1, BLU11, BLU12, etc. I've tried the following, to no avail, as you can see I've tried nested and un-, and pound signs and stars as the wildcards. It works to convert the first series (BLU), but won't cycle past that to select the next route in the series. Some of these sheets have up to 12 Route codes with as many as 38-43 ID's per Route, and they vary, hence the need for Wilds. Any ideas? I am also posting a sample workbook with the code attached if ya wanna play around with it. Thanks in advnce for any help!

Please Login or Register  to view this content.

200809_MONTHLY_HELP.xlsm

Shennin,
What are you trying to do?
After renumbering the column 1, are you trying to sort or does it already come in the sorted order?
Do you just want to renumber column 1 from BLU1 to BLU01?

If so, Here are some questions before I can recommend an answer:
1. does the id always come prefixed by Alpha characters such as BLU, RED, GRE etc.?
2. Is the total length of the id 5 characters?
3. Do you mind adding an additional column next to column 1 and then work with the sheet?
/el

Here is a suggestion.
1. Add a new column 'B' next to column 'A'

2. In the new blank cell B4 enter the formula =IF(LEN(A4)<5,MID(A4,1,3)&"0"&MID(A4,4,LEN(A4)-3),A4)

3. Copy from new Cell B4 down to however many rows you have in column B. The values in the new column 'B" will become from BLU1 top BLU01, BLU2 to BLU02 .... GRN1 to GRN01, GRN2 to GRN02 ... and so on.

4. Now sort on the new column 'B' and that should come out in the correct sort order you want.

Does this help?

Did you try this?

1. Add a new column 'B' next to column 'A'

2. In the new blank cell B4 enter the formula =IF(LEN(A4)<5,MID(A4,1,3)&"0"&MID(A4,4,LEN(A4)-3),A4)

3. Copy from new Cell B4 down to however many rows you have in column B. The values in the new column 'B" will become from BLU1 top BLU01, BLU2 to BLU02 .... GRN1 to GRN01, GRN2 to GRN02 ... and so on.

4. Now sort on the new column 'B' and that should come out in the correct sort order you want.

Does this help? Hope this is what you are looking for.

I am attaching the solution here so, you can see the formula on the new column "B".

If this works for you, please mark 'solved' for your query.

Hi, everlearning,

Thanks for your interest and suggestion. To answer your questions, I am trying to clean up some old data and prep it for export to an Access database we're developing. The spreadsheets already come in the sorted order we want, and your suggestion would work well for me if I were only going to be using the info in Excel. I will be sorting by the address column to clean up the data there, as well, then need to re-sort back to the original order for export to Access. So, unfortunately, I doubt an extra column with a formula would do the trick; the RouteID really kinda needs to be "hard-coded" as BLU01, etc.

Any other suggestions?

Shennin,
Just thinking. Since this is old data that you are trying to migrate, I am assuming the data is static and will not change anymore.
If that is the case, can you do the following:
1. copy the column 'A' from the sheet 'CONSOL' to a new sheet 'Sheet1'.
2. Perform the action I recommended to end up with a "Column 'B'" in 'Sheet1'.
3. Copy data from "Sheet1:Column 'B'" on to the original sheet "CONSOL:Column 'A'". Make sure you use 'paste special', only to transfer the data and not the formula on the 'Column 'A'.

Result: You will have the sheet 'CONSOL' with the date in "Column 'A'" replaced from 'BLU1" to 'BLU01', 'GRN1 to 'GRN01' ...

Attaching the document (V2) to show the end result (same number of columns with desired data in "Column 'A'". Check out the 'Sheet 1' as well.

Hope this will work for you.

Can't just give up!

/el

Bumpit

Can this even be done?

Here's something weird.... when I use the following code, Excel selects cell "BLU1". What am I doing wrong?

Please Login or Register  to view this content.

If the very last statement, "Range(Cells(4 + RowCount, 1)).Select" is left out, then Excel selects cell A4, the starting point. Any other ideas??

Shennin,
I am not a VB programmer However, You may have to answer this question and you may get to the source of your problem.

I have 2 questions:
1. Why are you hard coding 'BLU..' in your program. This has no relevance to wyhy you are ehaving hte problem.

2. Are you ignoring the fact that we now have 5 characters instead of floating 4 to 5 characters in the column 'A'.

I may not be making sense below. If so, please bear with me but, get the gist of my thought below. If my statements below sound absurd, I am not the person who can answer your question. Sorry!

Please remember you do not have 'BLU1' anymore. You only have 'BLU01', 'BLU02'...
Why three is a reference of 'BLU1' in your program code?

Also in your statement:
Selection.AutoFill Destination:=Range(Cells(4, 1), Cells(4 + 8, 1)), Type:=xlFillDefault

Does Cells(4,1) refer to the data in A4 for a length of 4 from character in position 1?
If that is the case then you'll have duplicate data because the result will be a 4 character text 'BLU0" for 9 rows of data.
If my question is true then you may want to say Cells(5, 1) - just guessing by logic.

Check that out, as I said, I am not a VB programmer and do not know the program commands.

Hi, everlearning,

Your suggestion to create a new work sheet might be a good workaround if I can't figure out how to make the wildcard statements fire correctly, thanks.

To answer a couple of your questions:

"BLU", "GRE, etc. are references to a color code for collection routes my company runs. The explanation of what I'm doing and all the data involved is rather lengthy, so I'll spare the details, but the reference BLU1 refers to the first stop on the Blue Route, BLU2 is second, etc. Thus it's hard coded. The reference BLU1 in the code tells VBA to look for RouteID "BLU1" and change it to "BLU01".

In the statement "Selection.Autofill etc.", Cells(4, 1) is an alternate means of referring to Cell A4 that allows you to tell VBA to add rows or columns. For example, if Cells(4, 1) (Cell A4 in normal parlance) is selected, you can move to Cell A10 by saying "Cells(4 + 6, 1).Select", or in the case of what I'm trying to accomplish, having VBA count the number of rows as a variable (RowCount) based on a criteria (how many rows have "BLU" in the RouteID?), then adding the Row Count to the Cell reference, thus: "Cells(4 + RowCount, 1).Select". My problem seems to stem from improper syntax, and that's where I need the help.

Sorry! As I said earlier, I cannot help you with the syntax.
I see this is a subroutine and I also see a few variables that are not local to the Sub routine such as Type, xlFillDefault, FormulaR1C1 etc.

Also when you say Excel selects "BLU1" I do not understand what variable gets the value "BLU1", is it Route.FormulaR1C1 getting assigned this value? (I do not see any variable should have the value 'BLU1" because that does not exist anywhere in the reformatted column 'A'. Is it possible that it is getting assigned outside this subroutine and the routine always failed due to some reason and the residual value is 'BLU1'? these are questions only a VBA programmer can ask.

Also check your 'For loop'.

In the For loop
you step through each 'Route' in the name "CONSRouteID", you check for the value of FormulaR1C1 (If Route.FormulaR1C1 = "BLU1" Then) but, you do not make teh new value assignment ActiveCell.FormulaR1C1 := "BLU01" (you only say ActiveCell.FormulaR1C1 = "BLU01").

Should this be ':= "BLU01"' instead of '= "BLU01"'?

My 1 cent contribution which may not even have that value.