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,416
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,081,527
Messages
5,359,295
Members
400,524
Latest member
Excelbat

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top