Web Development

General discussion


comparing identical values in 2 cells

By lakshmisushma ·
In a workbook I have 2 sheets once containing
a list of items and another containing transactions. Based on the list, using a macro, when i write code for comparing, the condition shows as false even though they are same. Like in one sheet I have BC107 and in another I have BC107, it works as though
both are different and are not equal.

We need to generate reports if this works.

Could anyone help .


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

comparing identical values in 2 cells

by DKlippert In reply to comparing identical value ...

You can compare the two values. It would help to see the code you are using.
Here is a quick example that works. I didn't define any of the variables, so they're variable and able to deal with text or numbers. Did you Dim your variables as, say Integer?

Sub Compare()
Item1 = Worksheets("Sheet2").Range("A1").Value
Item2 = Worksheets("Sheet3").Range("A1").Value
If Item1 = Item2 Then Worksheets("Sheet2").Range("A4").Value = "Same value"
End Sub

Collapse -

comparing identical values in 2 cells

by SanKulPune In reply to comparing identical value ...

Hello Sushma,

Comapring values is a tricky business. Excel cell formatting makes it more complex.

1) Comparing strings: A space here and there makes all the disfference. You may need to use the trim function to strip all the un-necessary spaces in both the strings.

Thus, to compare string values in two cells (A1 on Sheet1 and A2 on Sheet2) you should be using:
=trim(Sheet1!A1) = trim(Sheet2!A2)

2) Comparing numbers: Here again, due to cell formatting both cells may display same value (1.21) but actual values may be different (1.21 and 1.209). You need to decide the extent of comparison and then use appropriate functions.
As an example something like
=round(Sheet1!A2,2) = round(Sheet2!A2) will result in True.
3) Comparing dates: ditto as numbers.
Ths cells may display 1/1/01 but their actual values might be 1/1/01 10:35 and 1/1/01 10:36 which obviously are not equal.
= And ((Day(Sheet1!A1) = Day(Sheet1!A1)) (Month(Sheet1!A1) = Month(Sheet1!A1)) (Year(Sheet1!A1) = Year(Sheet1!A1))
will however result in True.

Your feedback, as to whether this helped or more assistance is needed, would be highly appreciated.
I would be glad to provide any more specific assistance you may need in this regard.

With best compliments,

Simple And Effective Solutions To Small Problems, No QuickFixes.

Collapse -

comparing identical values in 2 cells

by lakshmisushma In reply to comparing identical value ...

Hai SanganakSakha ,

Thanks for the interest U have shown in my question.I have used the ways U have suggested even before posting the question.But still they don't work.Any comparative code will execute correctly if we cross check and retype the value where it had not compared.This I find amazing. Can U help me why this occurs, Is there any way where in I need not retype. Even after keeping the format of the 2 cells same like size, font, decimals etc also the comparison doesnot work.
Awaiting Ur Reply.


Related Discussions

Related Forums