CODEWITHSUNDEEP
vbaexcel
FairlyLegit
FairlyLegit

Reputation: 243

If Statement not evaluating to True when comparing input date versus date on another workbook

I feel like there is probably a very simple solution to my problem, but for the life of me I can't find it. I have two workbooks where at some point it needs to compare a user entered date to a date already on the worksheet. I've extracted the problem to some pretty simple code.

Dim wbk As Workbook
Dim MyValue As Variant
MyValue = InputBox("Enter Date")
Set wbk = Workbooks("Some Workbook")
If wbk.Worksheets(1).Range("A1").Value = MyValue Then '<--- This is Evaluating to False
    MsgBox "True"
End If

Lets say the date I'm using is 1/1/2000. If I write Msgbox wbk.Worksheets(1).Range("A1") I will get 1/1/2000. I am entering 1/1/2000 into the input box, so for all intents and purposes the if statement should be evaluated to true.

Upvotes: 1

Views: 54

Answers (2)

Davesexcel
Davesexcel

Reputation: 6984

You variable should be Dim MyValue As String

When I use your code

    Dim wbk As Workbook
    Dim MyValue As String
    MyValue = InputBox("Enter Date")
    Set wbk = Workbooks("TestMe.xlsm")
    If wbk.Worksheets(1).Range("A1").Value = MyValue Then    '<--- This is Evaluating to False
        MsgBox "True"
    End If

It will find the value to be true.

Upvotes: 0

Brad
Brad

Reputation: 12255

This is a data type issue. Step through your code and watch the data types of each of these values. The value in the Range on your sheet is a Date and the value received from the input box is a string

enter image description here

You need to convert them to be the same data type.

Dim wbk As Workbook
Dim MyValue As Variant
MyValue = InputBox("Enter Date")
Set wbk = ActiveWorkbook
Dim inputDate As Date
If IsDate(MyValue) Then
    inputDate = CDate(MyValue)
    If wbk.Worksheets(1).Range("A1").Value = inputDate Then
        MsgBox "True"
    End If
End If

Upvotes: 3

Related Questions