Microsoft Excel - =IF(C5=$B$1,50,0) C5=DATE B1=SYSTEM DATE WHEN THE DATE CHANGES I LOOSE THE VALUE

Asked By KEN WALTERS on 10-Nov-12 07:56 AM
=IF(C5=$B$1,50,0)  C5=DATE B1=SYSTEM DATE WHEN THE DATE CHANGES I LOOSE THE VALUE
John D replied to KEN WALTERS on 10-Nov-12 11:27 AM
Hi
If C5 is a static date and B1 a dynamic date, for sure they will match only 1 day.
Give us more information, I can't tell what you're trying to do.
KEN WALTERS replied to John D on 11-Nov-12 07:13 AM
Hi  John

C5 :C10 IS A LIST OF PAYMENT DATE   B1 IS MY  SYSTEM DATE. THE IF STATEMENTS ARE IN D5:D10
WHEN  C5 AND B1 MATCH , D5 = £50


WHEN B1 CHANGES THE £50 DISAPPEARS. 
IS THERE ANYWAY TO STOP IT FROM CHANGING

MANY THANKS

KEN
John D replied to KEN WALTERS on 11-Nov-12 08:39 AM
This is a workbook event macro, it will run when you open the workbook .Right click on your sheet tab and select
 "View Code". On the left side, you should see in the VBA Project "This Workbook", double click on it and paste the macro on the white area to the right .
..........................................
Option Explicit
Private Sub Workbook_Open()
Dim lastrow As Double, i As Double
lastrow = Cells(Cells.Rows.Count, 3).End(xlUp).Row
For i = 5 To lastrow
If Cells(i, 3) = Range("$B$1").Value Then
Cells(i, 4) = 50
End If
Next i
End Sub

KEN WALTERS replied to John D on 12-Nov-12 12:08 PM
Hi  John

Thank you for your help.

I followed your instructions and  it works great entering 50 in the cell to the right
.
Can I change the macro in some way to take in to account the different payments.


Once again  Many Thanks

Ken
John D replied to KEN WALTERS on 12-Nov-12 03:37 PM
Hi Ken
The macro I give you assume you will open the worksheet everyday. If you skip days, you will miss out on information.Don't forget that. That macro can be run with a button on the page if you prefer, your choice.   
Back to your question, if on the same row you have the price, let say column A, change this line on the macro.
"Cells(i, 4) = 50"  to "Cells(i, 4) = Cells(i, 1)". The one "1" in the formula is the column you want to retrieve the information. one is column A, 4 is column D.
BTW,  you're welcome, glad to help if I can
John