Prev: Linking Large Text Database to Excel
Next: shortcut keys
From: Anders on 5 May 2010 21:57
Hi All,

I have the following code to compare two lists for the same record. If the
same record is found, it's highlighted (eg anders(a)gmail.edu=
anders(a)gmail.edu; both get highlighted) and works perfectly. However, if
the records are as follows: Anders(a)gmail.edu and andDERS(a)gmail.edu, the code
doesn't match these two as equals because of the case changes.

I'm doing these as batch entries and it would be laborious to change to
lowercase all of the records. Is it possible to match regardless of
upper/lower case?

TIA!
Anders

sub exception_match ()

Dim oldupdate As Range, oldcell As Range

Dim newupdate As Range, newcell As Range

Dim Found As Boolean


Set oldupdate = Range("Table2")

Set newupdate = Range("Table1")

oldupdate.Interior.ColorIndex = xlColorIndexNone
newupdate.Interior.ColorIndex = xlColorIndexNone

For Each oldcell In oldupdate

Found = True

For Each newcell In newupdate

If oldcell.Value = newcell.Value Then

oldcell.Interior.ColorIndex = 6
newcell.Interior.ColorIndex = 6

Exit For

End If

Next

If Found Then

End If
Next

End Sub

From: JLatham on 5 May 2010 22:23
Change this line
If oldcell.Value = newcell.Value Then

to

If UCase(Trim(oldcell.value)) = UCase(Trim(newcell.value)) Then

This will not only ignore case (by changing both to all uppercase, but will
also make sure a leading/trailing blank or two in either one is also ignored.


"Anders" wrote:

> Hi All,
>
> I have the following code to compare two lists for the same record. If the
> same record is found, it's highlighted (eg anders(a)gmail.edu=
> anders(a)gmail.edu; both get highlighted) and works perfectly. However, if
> the records are as follows: Anders(a)gmail.edu and andDERS(a)gmail.edu, the code
> doesn't match these two as equals because of the case changes.
>
> I'm doing these as batch entries and it would be laborious to change to
> lowercase all of the records. Is it possible to match regardless of
> upper/lower case?
>
> TIA!
> Anders
>
> sub exception_match ()
>
> Dim oldupdate As Range, oldcell As Range
>
> Dim newupdate As Range, newcell As Range
>
> Dim Found As Boolean
>
>
> Set oldupdate = Range("Table2")
>
> Set newupdate = Range("Table1")
>
> oldupdate.Interior.ColorIndex = xlColorIndexNone
> newupdate.Interior.ColorIndex = xlColorIndexNone
>
> For Each oldcell In oldupdate
>
> Found = True
>
> For Each newcell In newupdate
>
> If oldcell.Value = newcell.Value Then
>
> oldcell.Interior.ColorIndex = 6
> newcell.Interior.ColorIndex = 6
>
> Exit For
>
> End If
>
> Next
>
> If Found Then
>
> End If
> Next
>
> End Sub
>
From: Jim Cone on 5 May 2010 22:46

You could also use the StrComp function.
-or-
Adding "Option Compare Text" at the top of the module would work.
--
Jim Cone
Portland, Oregon USA

 | 
Pages: 1
Prev: Linking Large Text Database to Excel
Next: shortcut keys