(in)definite article and leading/trailing space remover needed

[KHashmi316]

Hello KHashmi316,

Have you set your System Security level to medium? If it is set to Hig
the Macro won't run.

Sincerely,
Leith Ross

Hi, Leith:

Security is at medium. I run dozens of macros on a daily basis, so
don't believe this is the issue -- not unless there is somethin
particular regarding *your* macro and security

 

[Leith Ross]

Hello KHashmi316,

The macro runs fine on my machine. Send me a copy of your Workbook and
let me look it over to find the problem. My e-mail is
(e-mail address removed)

Thanks,
Leith Ross

 

[Guest]

The End If has to be there. I think the following line may be the issue

And (InStr(1, x.Value, " ", vbTextCompare) - 1 > 0) Then
x.Value = Trim(Replace(x.Value, y, "", 1, 1, vbTextCompare))
Exit For
End If


If Then statements can appear on one line. If so, you don't need the End
If. If the IF/THEN is being treated as one line then the Exit For will be
outside of the IF statement, causing the macro to end after looping through
only the first element of Array1 (which is "The"). In this case, the Then
statement has two lines of code requiring the End If.

Make sure the code appears exactly as I've written it above. Also, you
don't need to include lowercase "a" "an" "the" in the array. For comparison
purposes the macro converts everything to uppercase (but doesn't change how
it appears in the spreadsheet).

 

[KHashmi316]

The End If has to be there. I think the following line may be the
issue

And (InStr(1, x.Value, " ", vbTextCompare) - 1 > 0) Then
x.Value = Trim(Replace(x.Value, y, "", 1, 1, vbTextCompare))
Exit For
End If


If Then statements can appear on one line. If so, you don't need the
End
If. If the IF/THEN is being treated as one line then the Exit For will
be
outside of the IF statement, causing the macro to end after looping
through
only the first element of Array1 (which is "The"). In this case, the
Then
statement has two lines of code requiring the End If.

Make sure the code appears exactly as I've written it above. Also,
you
don't need to include lowercase "a" "an" "the" in the array. For
comparison
purposes the macro converts everything to uppercase (but doesn't change
how
it appears in the spreadsheet).

Hi, JMB:

Hoorah -- that worked! One more tweak I was thinking about was a way to
copy whatever (in)definite article the macro finds at the beginning of
the title, and concatenate it to the end of the title, such as:

The Scarlet Letter

to:

Scarlet Letter, The

By and large, however, article removing was my main concern. If you
have a quick suggestion, that's fine, but this new tweak is not that
critical.

Thx, again!

 

[Guest]

I'm glad that worked out okay.

To put the article at the end find this line

X.Value = Trim(Replace(X.Value, Y, "", 1, 1, vbTextCompare))

and add & ", " & Y

so that you have

X.Value = Trim(Replace(X.Value, Y, "", 1, 1, vbTextCompare)) & ", " & Y

 

[KHashmi316]

I'm glad that worked out okay.

To put the article at the end find this line

X.Value = Trim(Replace(X.Value, Y, "", 1, 1, vbTextCompare))

and add & ", " & Y

so that you have

X.Value = Trim(Replace(X.Value, Y, "", 1, 1, vbTextCompare)) & ", " & Y

Hi, JMB:

Works great!

Thx again!