Hello Paul-

I just want to express appreciation for your help! I don't want to appear
ungrateful, just other things have popped across my desk since my original
post. I plan on looking at this very soon!

Regards,
--
Steve Belville
Clinical Information Systems Trainer


" wrote:

Hi
Just to see how your billing codes are separated, paste this code into
a code module. Replace "A1" in the tester sub with the cell address of
one of the cells containing multiple billing codes. Run the tester sub
and see if you get msgboxes with each billing code in turn. Note that
Chr(10) is a return character.


Sub tester()
Dim myCollection As Collection
Dim Item As Variant
Set myCollection = ParseString(Range("A1").Value, Chr(10))
For Each Item In myCollection
MsgBox Item
Next Item
End Sub

'Parses a string into into pieces and stores as a collection
'separator is the character separating string elements
Function ParseString(myString As String, Separator As String) As
Collection
Dim PlaceComma As Integer 'Identifies position of first comma in a
string
Dim TempString As String, ItemString As String
Dim NoDupes As New Collection
Application.ScreenUpdating = False
On Error Resume Next
If Trim(myString) < "" Then
TempString = Trim(myString)
PlaceComma = InStr(TempString, Separator)
If PlaceComma = 0 Then
NoDupes.Add TempString, TempString
Else
TempString = TempString & Separator
'While a comma exists in the string
Do While PlaceComma 0
'Get rid of any leading comma's
Do While PlaceComma = 1
TempString = Trim(Right(TempString,
Len(TempString) - 1))
PlaceComma = InStr(TempString, Separator)
Loop
'If that leaves an empty string, leave the loop
If PlaceComma = 0 Then Exit Do
ItemString = Trim(Left(TempString, PlaceComma -
1))
NoDupes.Add ItemString, ItemString
TempString = Trim(Right(TempString,
Len(TempString) - PlaceComma))
PlaceComma = InStr(TempString, Separator)
Loop
End If
End If
On Error GoTo 0
Set ParseString = NoDupes 'could be empty
Set NoDupes = Nothing
End Function

regards
Paul

On Feb 26, 8:43 pm, Steve wrote:
Hello Paul-

Thanks again for your quick repsonse!

As I answered Tom, when I paste this into a word doc (as if I were using it
as a script) I see hard returns after each of the codes.

They are asking for the new rows to appear in the original spreadsheet with
the original row containing the first billing code, with each row containing
the remaining records.

This is a repetitive job, and I actually have a macro developed by someone
else that seems to separate the rows for the most part but it dosen't do this
consistantly (especially when both of the adjacent columns contain cells of
multiple codes) and it does not copy the preceding columns into the new
record rows.

Regards,
--
Steve Belville
Clinical Information Systems Trainer



" wrote:
Hi
How are the billing codes in a cell separated/ commas, colons,
space...?
Where do you want the separated rows to go - a new sheet or replace
the exisiting data with rows having only one billing code each?
Is this a one off job (maybe can be done without code) or something
you will have to do a lot (requiring code)?

regards
Paul

On Feb 26, 8:05 pm, Steve wrote:
I am looking for help in developing a billing application macro. I have a
spreadsheet with records in rows. At the end of rows are three columns with
cells that may have multiple billing code entries within each cell. For
example, Column M may have three billing codes, and column N may have four..
So in combining both columns, a total of seven records need to be created,
including the original one.

In essence, what the user has requested is to take each of those billing
elements, create distinct rows for each one and then copy the remaining
elements from the exisiting cells in the row to each of the newly created
records.

If this doen't make sense, I would be happy to explain in more detail to any
interested party.

Regards,
--
Steve Belville
Clinical Information Systems Trainer- Hide quoted text -

- Show quoted text -