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

Adding additional rows in VBA based on character strings
 

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" wrote in message
...
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