Excel and VBA Sorting

[comconrk]

I am having trouble with the following code. I am comparing two different worksheets and adding a record to one if it does not have a correspoinging vendor name.
This logic seems to work, except for embedded apostrophies. At that time the > does not seem to work properly.
Do I need to do something differently?

Thanks very much.


If UCase(Cells(jmast, 1)) > UCase(Vendor) Then

found = False
Exit For
End If

 

[PHV]

Have you tried LCase instead of UCase ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TonyJollans]

Hi comconrk,

Whether you use UCase, LCase, or no case conversion at all this could be a problem. Apostrophes (character code 39) will always be considered less than alphabetic characters.

To help needs more information about how you would like to treat the apostrophes (and any other non-alphabetic characters you may have - full stops, commas, parentheses?). Do you want to ignore them (should O'Grady come just before Ogre, for example)? Or what?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[comconrk]

Sorry, I got tied up and did not respond to this earlier. I did try the LCASE and did get the same results. What I am trying to do is to look for company names from two different Excel spreadsheets. Both are sorted in ascending order using the normal Excel sort. However, when I use logic as follows, where Vendor is the company from the second Excel worksheet.
If LCase(Cells(jmast, 1)) = LCase(Vendor) Then

This logic seems to use a different sequence than the Excel sort. The Excel sort will put A 1 Vac before A Bear Company, but the logic above seems to do the opposite.

I'm not certain what is going on.

Again,sorry for the delay, but thanks for your help.

 

[PHV]

You may try something like this:
If StrComp(Cells(jmast, 1), Vendor, vbTextCompare) > 0 Then

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Hi comconrk,

If you put ..

[COLOR=darkblue]Option Compare Text[/color]

.. in the Declarations section at the top of your code module, you should find your comparisons use the same sequence as the worksheeet Sort.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[comconrk]

Thanks to all of you, it is now working! I have used the Declarations section statement, the string comparison and the LCASE and now the macro works well.

Again, many thanks!