convert wildcard to new rows and add values

[Brianfree]

I have reposed this in the correct fourum now...

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

 

[Brianfree]

I have found the following code which creates duplicate rows, I think this is half way there.. I just need to now replace in sequence every ? with values from 0-9, or 0-99, 0r 0-999 etc..

Sub CopyData()   
Dim lRow As Long   
Dim RepeatFactor As Variant   

    lRow = 1   
    Do While (Cells(lRow, "A") <> "")   
           
        RepeatFactor = Cells(lRow, "B")   
        If ((RepeatFactor > 1) And IsNumeric(RepeatFactor)) Then   
                   
           Range(Cells(lRow, "A"), Cells(lRow, "B")).Copy   
           Range(Cells(lRow + 1, "A"), Cells(lRow + RepeatFactor - 1, "B")).Select   
           Selection.Insert Shift:=xlDown   
              
           lRow = lRow + RepeatFactor - 1   
        End If   
       
        lRow = lRow + 1   
    Loop   
End Sub

Many thanks

Brian

 

[SkipVought]

Brian,

I would not INSERT. That table is your source data. I'd put your data in a new table.

So what's in column B that you're assigning to RepeatFactor? How did that get there?

Skip,

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

 

[Brianfree]

Hi, I have a formula to count how many question marks..

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

then using this formula to calculate how many times the row needs to be copied... (column b)

=POWER(10,G2)

this repeats / duplicates the rows x times..

Just need to now replace ??? with 000, 001, 002, etc.. all the way up to 999 then move on to the next ??

Hope this helps??

Many thanks

Brian

 

[SkipVought]

Well then, can't you use that value to count as well?

So 255??? Is really 255,000 plus values from zero to 999 on each row of the thousand, right?

Skip,

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

 

[Brianfree]

Hi, yes this is correct...

255??? is 25000 plus values from zero to 999 on each row of the thousand

2444?? is 244000 plus values from zero to 99 on each row of the hundreds

Many thanks

Brian

 

[strongm]

Brian, Skip is trying to lead you to figure this out for yourself, rather than providing a pat answer for you.

 

[SkipVought]

Brian, since you're a programmer, I figure you must have the chops for logic and math. I would not want to insult a fellow programmer by providing stuff you would have learned in a 101 course.

Skip,

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