Comparing the value of two cells with VBA: not working

Kentetsu

Well-known Member
Joined
Jan 22, 2004
Messages
520
Attempting to compare two cells to see if they match. One has a date entered by the user, the other has a date provided by a Vlookup. I can't seem to get it to recognize an actual match no matter what I try.

Code:
If .Range("B11").Value <> .Range("C11").Value Then
        MsgBox "Code Date Info does not Match!", vbOKOnly
        Exit Sub
End If

I've played around with the formatting of both cells to no avail. Thanks for your thoughts on this...
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
It's possible that one has time appended to it, thus not allowing them to be matched. It also might be that the user is inputting it in such a way that it's not a date format anymore. To get around these potential errors:

Code:
        If Round(CLng(.Range("B11").Value), 0) <> Round(CLng(.Range("C11").Value), 0) Then            MsgBox "Don't Match", vbOKOnly
        End If
This should convert them to the same format and round off any potential time additions. Other than that, I don't know.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,206
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Attempting to compare two cells to see if they match. One has a date entered by the user, the other has a date provided by a Vlookup. I can't seem to get it to recognize an actual match no matter what I try.

Code:
If .Range("B11").Value <> .Range("C11").Value Then
        MsgBox "Code Date Info does not Match!", vbOKOnly
        Exit Sub
End If

I've played around with the formatting of both cells to no avail. Thanks for your thoughts on this...
Is what you posted within a With-End With construct that references the sheet those ranges are on? If yes, try this test:
In the Immediate Window type:
?IsDate(Activesheet.Range("B11")) and press enter - what do you get?
Repeat with C11 substituted for B11 - what do you get?
 

Kentetsu

Well-known Member
Joined
Jan 22, 2004
Messages
520
Hi Joe! :)

You are correct in your assumption that this is contained within a With-End With construct.

I tried your test, and it came up True.
I tried the same test on C11 and it also came up True.

Thanks for your help. Here's the entire construction so far:

Code:
Option Explicit
Public Sub VerifyEntry()

With Sheets("Sheet1")
    If .Range("B8").Value <> .Range("C8").Value Then
        MsgBox "Piece Printed Package Info does not Match!", vbOKOnly
        Exit Sub
    End If
        
    If .Range("B9").Value <> .Range("C9").Value Then
        MsgBox "Case Printed Package Info does not Match!", vbOKOnly
        Exit Sub
    End If
        
    If .Range("B10").Value <> .Range("C10").Value Then
        MsgBox "Case Number Info does not Match!", vbOKOnly
        Exit Sub
    End If
        
    If .Range("B11").Value <> .Range("C11").Value Then
        MsgBox "Code Date Info does not Match!", vbOKOnly
        Exit Sub
    End If

    If .Range("B13").Value <> .Range("C13").Value Then
        MsgBox "Establishment Number Info does not Match!", vbOKOnly
        Exit Sub
    End If
        
End With


End Sub

All other checks perform as expected, only the Code Date check is throwing up unexpected results...
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,206
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Then as suggested in post #2, if you only want to compare the calendar dates (ignoring the exact time of day for those dates) try changing the offending lines to this:
Code:
If Int(.Range("B11").Value) <> Int(.Range("C11").Value) Then
        MsgBox "Code Date Info does not Match!", vbOKOnly
        Exit Sub
End If
 

Kentetsu

Well-known Member
Joined
Jan 22, 2004
Messages
520
My apologies NeonRedSharpie, I totally missed your response. That did the trick to get it working.

Thank you for your assistance, both of you.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,971
Messages
5,525,967
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top