Small code problem...skipping found variable

Hlatigo

Well-known Member
Joined
Jun 3, 2002
Messages
677
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.


Code:
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

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

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


Tony
 

Hlatigo

Well-known Member
Joined
Jun 3, 2002
Messages
677
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,135
Office Version
365
Platform
Windows
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.
 

Forum statistics

Threads
1,077,831
Messages
5,336,653
Members
399,094
Latest member
Learner2019

Some videos you may like

This Week's Hot Topics

Top