Excel macro to convert / create data based on a whildcard...

[Brianfree]

Hi, i am trying to find out how to convert / create some new data in excel based on the following example...

My data looks like this...

225???, £10.00, £20.00
22505?, £15.00, £23.00
etc...

I want to run a macro or something to convert / create new data. For each instant of a '?' i need excel to replace with 0-9; the result needs to look like the following...

225000, £10.00, £20.00
225001, £10.00, £20.00
to
225999, £10.00, £20.00
225050, £15.00, £23.00
225051, £15.00, £23.00
to
225059, £15.00, £23.00

Please can anyone help?

Many thanks

Brian

 

[SkipVought]

Hi,

So what code/logic do you have thus far?

Where are you stuck?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Brianfree]

Hi, I don't know where to start to be honest. Was thinking if excel detects a ? then for each of them, duplicate the line and replace the ? with a value from 0 to 9

But not sure if this is the best to do it ;-(

Sorry...

 

[combo]

Solution depend on the level of automation you need. If you plan to use the same worksheet, the steps for a single reference row:
1) count the number of question marks in a cell,
2) add 10^n-1 rows below,
3) copy down the contents to newly added rows,
4) in each row in a range consisting of reference row and its copies run a loop with counter (i) from 0 to 10^n-1, replace n last characters by Format(i, String(n, "0"))

combo

 

[Andrzejek]

More information needed for "replace the ? with a value from 0 to 9"
Do you want to replact ? with 7 (always)? Probably not. Use a random number? Maybe.
So what would be the rule?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Brianfree]

HI, I just need to replace every instance of a ? with a new row of the same data but with a 0 - through to 9?

Hope this helps? - I'm struggling!

Many thanks

Brian

 

[SkipVought]

Hey Brianfree (Programmer), and I emphasize the latter, you need to do some heavy lifting. Please state the logic in terms of pseudo-code. No one is going to gift a programmer the solution. This has all been very nebulous.

You should have posted this in forum707 since you're asking for coding help, I assume.

So, again, where is your logic/code so far developed?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Brianfree]

I HAVE A FORMULA TO COUNT HOW MANY ? IN A CELL..

=LEN(A2)-LEN(SUBSTITUTE(A2,"?",""))

FROM THIS I NEED TO ADD X ROWS UNDERNEATH AND REPLACE X WITH A VALUE FROM 0 TO NUMBER OF ?

SLOWLY GETTING THERE!

 

[SkipVought]

I HAVE A FORMULA TO COUNT HOW MANY ? IN A CELL

So shouldn't that tell you something about the number of rows you'll need for each?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Brianfree]

Hi, still struggling on doing this... out of my abilities... Is there anyone who could provide a demo / some code for me to play with?

Many thanks

brian

 

[vgulielmus]

Just for fun

Sub a()
    Dim firstDataRow As Long, FirstColumn As Integer, LastColumn As Integer
    Dim wb As Workbook, sh As Worksheet, cl As Range, cl2 As Range, i As Long, CurRow As Long, coun As Long, LastRow As Long, CurChar As Integer, CharPos As Integer, Digit As Integer, curString As String, finString As String, tempString As String
    Set wb = ActiveWorkbook
    Set sh = wb.ActiveSheet
    firstDataRow = 1 'first row # with 225???
    FirstColumn = 1 'Column # for 225???
    LastColumn = 3 'Number of Columns to be copied
    LastRow = sh.Cells(firstDataRow, FirstColumn).CurrentRegion.Rows.Count
    For CurRow = firstDataRow + LastRow - 1 To firstDataRow Step -1
        coun = UBound(Split(sh.Cells(CurRow, FirstColumn).Value, "?"))
        Set cl = sh.Range(sh.Cells(CurRow, FirstColumn), sh.Cells(CurRow, LastColumn + FirstColumn - 1))
        Set cl2 = sh.Range(sh.Cells(CurRow + 1, FirstColumn), sh.Cells(CurRow + 1, LastColumn + FirstColumn - 1))
        cl2.Resize(10 ^ coun - 1).Insert Shift:=xlDown, CopyOrigin:=cl.Copy()
        curString = sh.Cells(CurRow, FirstColumn).Value
        For Digit = 0 To 10 ^ coun - 1
            tempString = Format(Digit, String(coun, "0"))
            finString = curString
            CharPos = Len(curString) + 1
            For CurChar = 1 To coun
                CharPos = InStrRev(curString, "?", CharPos - 1)
                finString = Left(finString, CharPos - 1) & Mid(tempString, coun - CurChar + 1, 1) & Mid(finString, CharPos + 1)
            Next
            sh.Cells(CurRow + Digit, FirstColumn).Value = finString
        Next
    Next
End Sub

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[SkipVought]

This guy does not need a rote solution!

http://en.m.wikipedia.org/wiki/Rote_learning#Rote_learning_vs._critical_thinking

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[vgulielmus]

Basically, I've implemented combo's algorithm.
I respect you very much, so I apologize if I did something wrong.

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Brianfree]

Hi vgulielmus, I have just tried your code and it appears to work with the exception of...

cl2.Resize(10 ^ coun - 1).Insert Shift:=xlDown, CopyOrigin:=cl.Copy()

not sure what this bit is doing?

Many thanks

Brian

 

[vgulielmus]

Items 2 and 3 from combo's algorithm
2) add 10^n-1 rows below,
3) copy down the contents to newly added rows

I have tested the routine with :

[pre] A B C
1 225??? £10.00 £20.00
2 22505? £15.00 £23.00[/pre]

[pre] C D E F G
4 225??? £10.00 £20.00 11 111
5 22505? £15.00 £23.00 22 222[/pre]

[pre] A B C
1 11111? 11 111
2 2222?2 22 222
3 ?33333 33 333
4 4444?? 44 444
5 555?5? 55 555
6 666??66 66 666
7 ??7777 77 777
8 888??? 88 888[/pre]

I assumed that you don't have any column to the left of the one containing "?", but it's not so hard to extend the code in that sense.

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania