Small code problem...skipping found variable

[Hlatigo]

I have some code that searches for a certain value and once it is found it than looks for the current date at that point there is a vlookup and pulls in a price. The code is working but when it does have the current date it just skips of the statement that it should run. Any thoughts?

I thought i needed to format the column as date but that hasnt worked. thatnks for your help.

Sub price_updte()

    Sheets("proposal").Activate
    Range("D65536").End(xlUp).Select
    Range(Selection, Selection.End(xlUp)).Select
    With Selection
        .NumberFormat = "m/d/yyyy"
    End With

   Dim cell As Range
   For Each cell In Range("B1:B500")
      Select Case cell.Value
         Case 79 To 81, 142 To 143
               If cell.Offset(0, 2).Value = Now() Then
                  cell.Offset(0, 5).Value = WorksheetFunction.VLookup(cell.Offset(0, -5), _
                  Sheets("currentprice").Range("D12:G16"), 2, False)
               End If
      End Select
   Next cell
End Sub

 

[acw]

Hi

First thought - change now() to date. Now() will contain a time component but your data may not???


Tony

 

[Hlatigo]

Hi there Tony,

I thought about that too and I used so many different ways to format that as well. But still no go. I did use your method and it stills doesnt recognize that the two values are equal so it can execute the then statement.

I was thinking...the column with the current date is being populated by a input box...would that make much of a difference?

 

[Joe4]

the column with the current date is being populated by a input box...would that make much of a difference?

Make sure that the date is actually being entered as a date and not text. In an Excel formula, you can use DATEVALUE function to convert text to a date.