Compare cell to data in column if match exit sub

[DThib]

I am trying to write a VBA macro to catch if someone is trying to re-pull data.

Basically, my users cannot remember if they have pulled results for the current date into a worksheet.

I need to write a logic statement to compare the current date (sitting in Cell "W2" to the column of dates in column A.
I keep getting an error with the code I have when I run it.

     Dim WDate As Date
       WDate = Sheets("Sheet1").Range("W2").Value
         [B][COLOR=#b22222] If Sheets("Sheet1").Column("B") = WDate Then[/COLOR][/B]
[I]                 run code[/I]
          ElseIf Sheets("Sheet1").Range("B") <> WDate Then
           MsgBox "Current data already present go to Entry Form", vbInformation + vbOKCancel, "Already Entered"
          End If

Red text is the fail point.

DThib

 

[Joe4]

If Sheets("Sheet1").Column("B") = WDate Then

You cannot check to see if a whole column of values equals a single date. That does not make any sense.
What if some fo the values in that column equal the date, and others do not?

Do you want to check to see if the date exists ANYWHERE in column B?
Will the date you are checking, if it exists in column B, always be the last entry in column B?

 

[tyija1995]

You will need to loop through column B to check whether one of the cells equals to the value in cell W2. Then you can setup some code based on that but as it stands, like Joe said, you can't compare a whole column in that way.

 

[DThib]

Yes. This is a worksheet that appends data at the end. The column will hold the date of entry for any results drawn on that date for the row.
I need the code to review the column and if matched then run MsgBox. If no match, run the code.
The code "corrected" is below.

   Dim WDate As Date
       
      WDate = Sheets("Sheet1").Range("W2").Value
[B][COLOR=#b22222]           If Sheets("Sheet1").Column("A") <> WDate Then[/COLOR][/B]
            Call Workie
            Call WCode
            Sheets("Sheet1").Range("B2").Select
            Work_FRM.Show
            Unload Me
          ElseIf Sheets("Sheet1").Range("A") = WDate Then
           MsgBox "Current data already present go to Entry Form", vbInformation + vbOKCancel, "Already Entered"
          End If

Sorry for the typo.


DThib

 

[Joe4]

You didn't answer my last question.
Would the date you are looking for, if it appears in column B, always be the last entry in column?

The reason why I ask is because if that is the case, then we can simply check the last entry in column B instead of searching or looping through all of column B, which would be more efficient.

 

[DThib]

Yes. The last date pulled will always be the last date in the worksheet in Column A not B. My bad on the first code.
Either way, yes, it would be the last date in that column.

 

[Yongle]

If you simply want to check if the value in W2 is found anywhere in column A, try this

Sub Test()
    With Sheets("Sheet1")
        If WorksheetFunction.CountIf(.Columns("A"), .Range("W2").Value) > 0 Then
            MsgBox "Current data already present go to Entry Form", vbInformation + vbOKCancel, "Already Entered"
        Else
            ' *** place instructions to run your code here ***
        End If
     End With
End Sub

Note
- looping avoided with CountIf

 

[Joe4]

Sorry, I am dealing with some issues here at work right now.
Yongle's code structure should work, in cases where it is the last cell, or found anywhere in column A. So I think that should cover all bases.
Let us know if that does not work for you.

 

[DThib]

It worked, thanks!

DThib

 

[DThib]

So,

If I now need to loop through all values in column A. How can I write that?

Different workbook and user using some of the same code. Here it is again:

Private Sub Workit_CB_Click()


   'Dim WDate As Date
       
  'WDate = Sheets("Sheet1").Range("W2").Value
        If MLevelTB.Value = "" Then
            MsgBox "Please add info for pull", vbCritical + vbOKOnly, "Jumping into Work"
            GoTo Line1
        ElseIf MLevelTB.Value <> "" Then
           With Worksheets("Sheet1")
            If WorksheetFunction.CountIf(.Columns("A"), .Range("W2").Value) > 0 Then
              MsgBox "Current data already present go to Entry Form", vbInformation + vbOKCancel, "Already Entered"
            Else
                Call Workie
                Call WCode
                Sheets("Sheet1").Range("B2").Select
                Work_FRM.Show
                Unload Me
            End If
           End With
Line1:  End If


End Sub

DThib