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

 

[Yongle]

Why are you wanting to loop through all the values in column A?
- does something happen every time a cell passes\fails a test?

What is being tested?
What is the test?
What happens when the test is passed?
What happens when the test fails?

 

[DThib]

Hi Yongle,

The code will search a database copied to a hidden worksheet (MK_DB).
When this code is fired, it will first narrow the search by 2 factors (Name and item).

The next step should run to check if the item number (SPR) is present in the landing sheet (Sheet1) to be copied to, if it is then skip and check next.
It needs to add to the list any "new" SPR numbers.

Does that help?

DThib

 

[DThib]

Sub Workie1()


  Dim LastRow, SecondRow As Long
  Dim i As Long
  Dim j As Long, BatchP As Long
  Dim Sp As Variant, X As Variant
    'Loop through 5- 20 spr sets in separate macros for CK pull
    LastRow = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
    SecondRow = Sheets("SPR").Cells(Rows.Count, "A").End(xlUp).Row
    
        i = 1 + LastRow
        j = 1 + SecondRow
     For i = 1 To LastRow 'Each i In Sheets("Sheet1")
        If Sheets("Sheet1").Cells(i, 22) = "Kzyk, Mac" Then 'And Not Sheets("Sheet1").Cells(j, 2) = Sheets("SPR").Cells(i, 1) Then
            Sheets("SPR").Cells(j, 1) = Sheets("Sheet1").Cells(i, 2).Value
            If Sheets("Sheet1").Cells(i, 2).Value = Sheets("SPR").Cells(j, 1).Value Then
            End If
             If Sheets("Sheet1").Cells(i, 8).Value = "" Then
              Sheets("SPR").Cells(j, 2) = Sheets("Sheet1").Cells(i, 9).Value
             Else
               Sheets("SPR").Cells(j, 2) = Sheets("Sheet1").Cells(i, 8).Value 'AIC
             End If
             Sheets("SPR").Cells(j, 3) = Sheets("Sheet1").Cells(i, 16).Value 'SW Ver
             Sheets("SPR").Cells(j, 4) = Sheets("Sheet1").Cells(i, 24).Value 'Date Assigned
             If Sheets("Sheet1").Cells(i, 5) = 0 Then
               Sheets("SPR").Cells(j, 8) = ""
             Else: Sheets("SPR").Cells(j, 8) = Sheets("Sheet1").Cells(i, 5)
             End If 'SalesForce
             Sheets("SPR").Cells(j, 5) = Sheets("Sheet1").Cells(i, 18).Value 'DateSPREntered


           If Sheets("Sheet1").Cells(i, 10) <> "" Then
            Sp = Split(Replace(Sheets("Sheet1").Cells(i, 10).Value, "(", ")"), ")")
            With Application
              X = .Index(Sp, .Match("10", Sp, 0) + 1)
            End With
            If Not IsError(X) Then Sheets("SPR").Cells(j, 15).Value = X
           ElseIf Sheets("Sheet1").Cells(i, 11) <> "" Then
            Sp = Split(Replace(Sheets("Sheet1").Cells(i, 11).Value, "(", ")"), ")")
            With Application
              X = .Index(Sp, .Match("10", Sp, 0) + 1)
            End With
            If Not IsError(X) Then Sheets("SPR").Cells(j, 15).Value = X
           End If
            'Batch # Left(Cells(I, 10), BatchP + 1)
             Sheets("SPR").Cells(j, 21) = Sheets("Sheet1").Cells(i, 39).Value 'Date Occurred
             Sheets("SPR").Cells(j, 25) = Sheets("Sheet1").Cells(i, 31).Value 'Symptom
             Sheets("SPR").Cells(j, 30) = Sheets("Sheet1").Cells(i, 40).Value 'Submitted by Name
             Sheets("SPR").Cells(j, 18) = Sheets("Sheet1").Cells(i, 48).Value 'Failed Component Serial Number
             Sheets("SPR").Cells(j, 17) = Sheets("Sheet1").Cells(i, 49).Value 'Component Lot #
             Sheets("SPR").Cells(j, 34) = Sheets("Sheet1").Cells(i, 23).Value 'Status
         j = j + 1
        
        End If
     Next i


End Sub

Latest Attempt..
still no success, copies all cells not the new ones

DThib

 

[DThib]

bump