Adding additional rows in VBA based on character strings

[Guest]

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

 

[Tom Ogilvy]

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