case sensitive compare in excel with vba 2

[autex]

the code below works perfect unless the capital and lowercase don't match on otherwise identical words. Any advice?


Sub compareyears()
Dim i, j, k As Integer
Dim prev, curr As String
prev = Sheets("data").Cells(2, 1)
curr = Sheets("data").Cells(3, 1)

For i = 2 To Sheets(prev).Range("a1").End(xlDown).Row
For j = 2 To Sheets(curr).Range("a1").End(xlDown).Row
If Sheets(prev).Cells(i, 1).Value = Sheets(curr).Cells(j, 1).Value Then
For k = 2 To 10
If Sheets(prev).Cells(i, k).Value <> Sheets(curr).Cells(j, k).Value Then
Sheets(prev).Cells(i, k).Interior.ColorIndex = 42
Sheets(curr).Cells(j, k).Interior.ColorIndex = 42
End If
Next k
End If
Next j
Next i

End Sub

 

[xlbo]

what do you WANT to happen ?? do you want it to match whether or not the case is the same or to only find EXACT matches ??

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[PHV]

Replace this:
If Sheets(prev).Cells(i, 1).Value = Sheets(curr).Cells(j, 1).Value Then
By this:
If UCase(Sheets(prev).Cells(i, 1).Value) = UCase(Sheets(curr).Cells(j, 1).Value) Then
And this:
If Sheets(prev).Cells(i, k).Value <> Sheets(curr).Cells(j, k).Value Then
By this:
If UCase(Sheets(prev).Cells(i, k).Value) <> UCase(Sheets(curr).Cells(j, k).Value) Then

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

 

[TonyJollans]

Hi autex,

Alternatively, if you want all comparisons to be case sensitive add:
[blue][tt] Option Compare Binary[/tt][/blue]
at the start of your module (before the first Sub or Function)

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[xlbo]

Now THAT is a valuable tip Tony - please allow me to be the 1st (of many I would imagine) to give you a star for that !

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

Thanks Geoff,

It's a funny thing. I've been posting here a couple of years and sometimes posted things that have been quite a bit of work, but the things that impress the most are the little things that I sometimes hesitate to mention for fear of stating the obvious.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[xlbo]

LOL - join the club !!

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[mordja]

Tony,

Excellent, I had written a parsing module and assumed case sensitivity in string comparisons to be standard. No messy code arounds needed. Worthy of another *.

Mordja

 

[PHV]

And what about the 3rd argument of the StrComp function ?

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