If (two cells match) Then Statement

[nicolehalliday]

Hi,
I am having trouble getting my macro to run under the condition that two cells do not match. Does anyone see a problem with this statement?

If Sheets("highs").Range("EA11").Value = Sheets("daily").Range("A3").Value Then

Cell EA11 has the formula =WORKDAY(TODAY(),-1) and cell A3 has the formula =TEXT(A2,"MM/DD/YYYY"), but they return the same date and appear the same. My idea was that if the above statement is true, a msgbox appears saying "Updated", and if the condition is not met, then I will run a longer macro.

An alternative to this approach would be to have the macro run automically each day even if the spreadsheet is not opened that day... However I am very much a beginner and that sounds quite complicated. Anyways, help or suggestions are appreciated! Thanks

 

[njimack]

Your 2 cells dont contain the same value. One contains a date, the other contains text. You need to test EA11 against A2, not A3.

 

[nicolehalliday]

Thank you for the reply! Great suggestion but there must be something else that is wrong. Now I have a "type mismatch". Should I have '.Date' or '.Value' in the statement? Or should I 'dim' the cells first and then just reference their names in the statement? I've tried a few things with no luck. Here is the whole macro if it helps, but it always stops on the If statement.

Sub highs()

If Sheets("highs").Cells("EA11") = Sheets("daily").Cells("A2") Then
    MsgBox "Updated"
    
Else
    Columns("Z:Z").Select
    Selection.delete Shift:=xlToLeft
    Range("EA1").Select
    ActiveCell.FormulaR1C1 = "=WORKDAY(TODAY(),-1)"
    Range("EA2").Select
    ActiveWindow.SmallScroll Down:=9
    Sheets("daily ").Select
    Application.Run "BLPLinkReset"
    Range("D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("highs").Select
    Application.Run "BLPLinkReset"
    Range("EA11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

End If
End Sub

 

[JackDanIce]

I don't think you can use Cells("EA11"), try Range("EA11")

 

[njimack]

I can't see anything that would generate that error. Which line was highlighted when you went into Debug?

Your code was unecessarily selecting sheets and cells. I can't tell exactly what you're code is doing, but something like the below would be more efficient...

Sub highs()

Dim Last_Row As Long

If Sheets("highs").[B]Range[/B]("EA11").Value = Sheets("daily").Cells("A2").Value Then
    MsgBox "Updated"
    
Else
    Columns("Z:Z").Delete Shift:=xlToLeft
    Range("EA1").Formula = "=WORKDAY(TODAY(),-1)"
    
    Application.Run "BLPLinkReset"
    
    With Sheets("daily")
        Last_Row = .Range("D2").End(xlDown).Row
        .Range("D2:D" & Last_Row).Copy
    End With
    
    Sheets("highs").Select
    Application.Run "BLPLinkReset"
    
    Range("EA11").PasteSpecial xlPasteValues

End If
End Sub

 

[nicolehalliday]

Tried that, and a number of things... I understand if you can't really help me on this one without combing through my spreadsheet but I still really appreciate any suggestions or ideas!

 

[njimack]

Have you changed both instances of 'Cells' to 'Range'?
Sheets("highs").Range("EA11").Value = Sheets("daily").Range("A2").Value