Macro Conditional Statement

[hometeamjill]

Hi there,

I am a medical transcriptionist with very little programming experience, but
have written a series of macros to copy/paste reports into my client's
Word-based document. Unfortunately, although I've written some steps into
the macro which would make it difficult to paste one patient's report into
the wrong patient's demographics, it has still happened.

When part 1 of the macro runs, this is how the patient info lines look:

Correct:

32460 reagan xr chest (obtained from demographics in client's header)
32460 reagan xr chest (from our typed report)

Incorrect:

32460 reagan xr chest
42460 reagan xr chest

As you can see, in the Incorrect sample, the numbers don't match. From what
I've gathered so far, I need to insert the two patient info lines into a
table and have the program abort if the A1 cell of the table doesn't match
the A2 cell. I would like it to print out "ABORT" if it doesn't match and
just continue as normal if it does.

I would really appreciate any help I can get.

Thanks a lot!

 

[Jean-Guy Marcil]

Hi there,

I am a medical transcriptionist with very little programming experience, but
have written a series of macros to copy/paste reports into my client's
Word-based document. Unfortunately, although I've written some steps into
the macro which would make it difficult to paste one patient's report into
the wrong patient's demographics, it has still happened.

When part 1 of the macro runs, this is how the patient info lines look:

Correct:

32460 reagan xr chest (obtained from demographics in client's header)
32460 reagan xr chest (from our typed report)

Incorrect:

32460 reagan xr chest
42460 reagan xr chest

As you can see, in the Incorrect sample, the numbers don't match. From what
I've gathered so far, I need to insert the two patient info lines into a
table and have the program abort if the A1 cell of the table doesn't match
the A2 cell. I would like it to print out "ABORT" if it doesn't match and
just continue as normal if it does.

You have stated a solution that seems viable, but you have not stated which
part of this solution you are having problem with.
"I would like it to print out "ABORT"", where? In this two row table (which
cell?), below this table, on the screeen only?

Personally, I would reseacrch the why... Meaning.. Why does it sometimes
fail to print the right numbers? Then I would fix that instead of having an
Abort mechanism which indicates that you are letting yourself being defeated
by Word!
If the number must match in alll cases, why get them from two different
sources? Just copy the number from the same source twice.

If, on the other hand, this double source is there to verify/check against
external errors, then you do need the Abort mechanism, but, again, I do not
understand what kind of help you need.

 

[Helmut Weber]

Hi Hometeamjill,

maybe you are looking for something like that:

Sub Test567()
Selection.Bookmarks.Add "Test"
Selection.Paste
ActiveDocument.Bookmarks("Test").End = _
Selection.Range.End
With ActiveDocument.Bookmarks("Test").Range
If .Paragraphs(1).Range.Words(1) <> _
.Paragraphs(2).Range.Words(1) Then
MsgBox "abort", vbOKOnly
ActiveDocument.Undo 3
End If
End With
End Sub

Which adds a bookmark, pastes the text form the clipboard,
extends the bookmark til the end of the pasted text,
checks whether the first word in the first paragraph
of the pasted text is equal to the first word in
the second paragraph, issues a message and undos all.
Note that the number is a word in Word's definition,
and that that word has a trailing space.

Assuming that the text you are pasting contains paragraphs.


--

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Vista Small Business, Office XP

 

[hometeamjill]

Thanks for your response.

The patient numbers come from two different sources. A transcriptionist has
typed the number from a dictation and then her reports go to an editor to be
pasted into our client’s database. The editor types the patient number in my
client’s medical manager program which then pulls up a blank Word template
that contains the patient’s demographics at the top. At this point, she runs
the macro that pastes the report into that template. The macro pulls the
patient’s name and number from the demographics section at the top of the
template and places the number and name directly on top of the number and
name typed by the transcriptionist (please see example in original question).
The macro then highlights both of these lines and stops there. Then the
editor is supposed to make sure the numbers match before hitting the delete
key and deleting both lines. Occasionally the editor has transposed two
numbers and typed in the wrong patient number. If she doesn’t notice the
numbers don’t match, the report ends up getting pasted into the wrong
patient’s account.

So, the most important thing for me is to prevent the editor from continuing
with the pasting process if the numbers don’t match. It would be nice if the
macro was written to use a pop-up box that says something like “ABORT†so the
editor knows what’s going on if the numbers don’t match, but that’s not as
important as the first step.

Hopefully I explained that okay. Any advice would be greatly appreciated.

Thanks again!

 

[Jean-Guy Marcil]

Thanks for your response.

The patient numbers come from two different sources. A transcriptionist has
typed the number from a dictation and then her reports go to an editor to be
pasted into our client’s database. The editor types the patient number in my
client’s medical manager program which then pulls up a blank Word template
that contains the patient’s demographics at the top. At this point, she runs
the macro that pastes the report into that template. The macro pulls the
patient’s name and number from the demographics section at the top of the
template and places the number and name directly on top of the number and
name typed by the transcriptionist (please see example in original question).
The macro then highlights both of these lines and stops there. Then the
editor is supposed to make sure the numbers match before hitting the delete
key and deleting both lines. Occasionally the editor has transposed two
numbers and typed in the wrong patient number. If she doesn’t notice the
numbers don’t match, the report ends up getting pasted into the wrong
patient’s account.

So, the most important thing for me is to prevent the editor from continuing
with the pasting process if the numbers don’t match. It would be nice if the
macro was written to use a pop-up box that says something like “ABORT†so the
editor knows what’s going on if the numbers don’t match, but that’s not as
important as the first step.

Hopefully I explained that okay. Any advice would be greatly appreciated.

Place those in a table as you suggested.
Then, make sure that the first column contains only the patient number.
After that it is easy to compare the content of each cell.

If the content is different, use a message box to pop up a warning,
something like:

With Selection.Tables(1)
If .Rows(1).Cells(1).Range.Text = .Rows(1).Cells(1).Range.Text Then
MsgBox "ABORT, ABORT, mismatch in patient number... ABORT,
ABORT!!!", _
vbCritical, "Error"
Exit Sub
End If
End With

 

[Jay Freedman]

Place those in a table as you suggested.
Then, make sure that the first column contains only the patient
number. After that it is easy to compare the content of each cell.

If the content is different, use a message box to pop up a warning,
something like:

With Selection.Tables(1)
If .Rows(1).Cells(1).Range.Text = .Rows(1).Cells(1).Range.Text Then
MsgBox "ABORT, ABORT, mismatch in patient number... ABORT,
ABORT!!!", _
vbCritical, "Error"
Exit Sub
End If
End With

Hi, Jean-Guy,

Just a gentle nudge to use <> instead of = and to compare different
rows:

If .Rows(1).Cells(1).Range.Text <> .Rows(2).Cells(1).Range.Text Then

It might also be a good idea to wrap each of those expressions in a Trim()
function to avoid a mismatch only because of white space differences:

If Trim(.Rows(1).Cells(1).Range.Text) <> Trim(.Rows(2).Cells(1).Range.Text)
Then

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[hometeamjill]

Thank you so much. I'm so in over my head, and this should really help.

Take care!

 

[Jean-Guy Marcil]

Hi, Jean-Guy,

Just a gentle nudge to use <> instead of = and to compare different
rows:

Oooppss...
Guess how long it took me to write that reply...
But it's no excuse, I should be more careful...

Thanks for fixing my totally stupid code!