Excel VBA Question 1

[Nullsweat]

Hola :

I am new to this group so bear with me please : I have a sheet that updates with each new days info, it has a cell with whatever that days date is- in it. I then have a weeks worth(Sunday, Monday etc) of sheets that I want to pull info from that first sheet BASED on the date. The problem is caused by the fact that I dont want the Formula to be overwritten if the dates do not match


This is the Code I have tried-

Private Sub FORMULA3()
If (Sunday!A1 = Now) Then
Range("C4&quot.Formula = "=SUMIF(Calm!$N:$N,$B4,Calm!$I:$I)"
End If
End Sub

The error I get when I run my cursor over the highlighted portion of the code is - Sunday!A1 = <Object Required>

Next I tried-

Private Sub FORMULA3()
If Calm!A6 = Sunday!A1 Then
Range(&quot;C4&quot.Formula = &quot;=SUMIF(Calm!$N:$N,$B4,Calm!$I:$I)&quot;
End If
End Sub

I got the same error but now- Sunday!A1 = <Object Required> Calm!A6 = <Object Required>

I tried taking out the space between the Equals sign, tried putting quotes, looked up in the help, looked up in books both Formulas for 2002 and Bible, tried asking my colleagues, searched for it on metacrawler........I don't know.
I really appreciate any help you can give. :
Thanks :
Sean

 

[Zathras]

There are a couple of problems. First, comparing to Now will probably never be equal since it has a time component, so better to do a &quot;close enough&quot; type of test. Second, assuming &quot;Sunday&quot; is the name of a worksheet, the syntax needs some fixing up. Below is a version of your routine that does something. I leave it up to you whether it does the right thing:

Option Explicit

Private Sub FORMULA3()
    If Abs((Worksheets(&quot;Sunday&quot;).Range(&quot;A1&quot;) - Now)) < 0.02 Then
        Range(&quot;C4&quot;).Formula = &quot;=SUMIF(Calm!$N:$N,$B4,Calm!$I:$I)&quot;
    End If
End Sub

 

[Nullsweat]

THANKS :

I am assuming that Abs stands for Absolute? But why the - Now and why < .02?
If I may ask?
Sean

 

[Zathras]

&quot;Now&quot; returns the current date and time which is constantly being updated. When you compare &quot;Now&quot; as found in the spreadsheet with &quot;Now&quot; a retrieved during the macro execution, they will never be exactly the same. By subtracting one from the other you should get a very small number which may be either positive or negative depending on which &quot;Now&quot; has the more recent time. The absolute function turns a possible negative number into a positive one that can then be compared with some small constant.

The use of .02 is completely arbitrary. it represents two one-hundredths of a day (24 hours) so is equivalent to .48 hours or 28.8 minutes. If you like, you can use a smaller number. It doesn't really matter as long as it's small enough.