Campore 2 cells (want non-case sensitive)

[jjlafond]

Hello,

I have data set up on 2 sheets (each cell similar to 12345MD, and right now I am using an If statement like this:
If Cell1 = Cell2 Then
Do stuff


This requires the text in each cell to be an EXACT match.

I would like to be able to have the condition return true regardless of case so that:
12345MD = 12345md TRUE
12345MD = 12345mD TRUE

How could I make this happen?

 

[Jonmo1]

Try

=If(A1=B1,"They're the same","Not the same")

A standard Cell1=Cell2 is NOT case sensitive.

 

[jjlafond]

The cells are still case sensitive.

Maybe my issue comes from this...
The first cell being compared is a standard cell entry (in a "Merge & Center" cell). The second cell being compared was imported from another document, and had a TextToColumns operation performed on it (without this step even an EXACT match doesn't = TRUE). Could that have messed with the formatting and cause them not to match?

 

[Jonmo1]

No, even with all that. It still boils down a simple cell to cell comparison
=A1=B1
And that is NOT case sensitive.

Perhaps there is some custom formatting in the cells making the cells 'Appear' differently than they actually are.
Try setting the format to general for both cells involved in the comparison.
This won't change the result of the cell=cell comparison.
But might reveal that they really aren't the same when you think they are... (or vice versa)

 

[jjlafond]

Hmm...

I ran my macro, and it spit out a mismatch. I changed nothing, copied in =IF('Mold X & R Template'!F2='Part Data'!B13,"They're the same","Not the same") to a random cell, and checked to see of they matched. The macro says mismatch, but the IF statement says They're the same. I have no idea why.

 

[Jonmo1]

So it's a Macro that's having the issue, not the formula.
That's entirely different.
Sorry, I had it stuck in my head that you were usign a formula..

VBA IS case sensitive in text comparisons.

Put this at the top of your module, above all other code.

Option Compare Text


Note, this will make ALL code in that module NON Case sensitive.
That may or may not be desireable...

To make it just for that specific code to be not case sensitive, try
Ucase(Cell1) = Ucase(Cell2)
or
Lcase(Cell1) = Lcase(Cell2)

 

[jjlafond]

That did it! I used the Option, but I'll keep Ucase and Lcase in mind for future VBAs. Thank you!

 

[Jonmo1]

Glad to help, thanks for the feedback.