Adding additional rows in VBA based on character strings

Hi all,

I'm trying to write some VBA code that will allow me to evaluate cells, find
cells that include "#" somewhere in the string and duplicate the entry and
modify the "#" to become a symbol. Here's an example.

A list of items:

Monkeys
Cinnamon
My#Number
Dogs

Would become:

Monkeys
Cinnamon
My1Number
My2Number
Dogs

I tried using the replace function, but I'm not having much luck with it.

Any help would be greatly appreciated,

Thanks in advance,

Mike

Sub DoctorData()
Dim i as long, cell as Range
i = 1
for each cell in selection
if instr(cell.Text,"#") then
cell.Value = application.Substitute(cell.Text,"#",i)
i = i + 1
end if
Next
End Sub


--
Regards,
Tom Ogilvy

"Mike" <[email protected]> wrote in message
news:[email protected]...
> Hi all,
>
> I'm trying to write some VBA code that will allow me to evaluate cells,
find
> cells that include "#" somewhere in the string and duplicate the entry and
> modify the "#" to become a symbol. Here's an example.
>
> A list of items:
>
> Monkeys
> Cinnamon
> My#Number
> Dogs
>
> Would become:
>
> Monkeys
> Cinnamon
> My1Number
> My2Number
> Dogs
>
> I tried using the replace function, but I'm not having much luck with it.
>
> Any help would be greatly appreciated,
>
> Thanks in advance,
>
> Mike