Difficulty Subtracting Dates in VBA

[RunTime91]

I can't believe I'm here for this...but I can't get this to work

Dim Rng As Range
Dim Rng1 As Range

Set Rng = Worksheets("Sheet1").Range("D4")
Set Rng1 = Worksheets("Sheet1").Range("D4") - 2

The value in "D4" is a date - The error I'm getting is an 'Object Required' Error on the Set Rng1... line

Clearly I'm missing something - but I'm not seeing it

Thanks in advance for the humility about to come my way

 

[Joe4]

Rng1 is a Range, not a value. So you cannot subtract 2 from a range (and use you the "Set" command for setting objects, such as ranges - values are not objects!).

Try something like:

Dim Rng As Range
Dim Val1 as Double

Set Rng = Worksheets("Sheet1").Range("D4")
Set Val1 = Rng.Value - 2

 

[RunTime91]

...And there it is...

Geez, Sorry to bother you for something so menial, Joe

But I'm glad you were there, because I was running a marathon around that simple solution

Thank You again, Joe...

 

[Joe4]

You are welcome!
No worries, we all sometimes overlook the little things (feel free to blame it Monday!).

 

[Norie]

eh, doesn't this code give a similar error to the original code.

Dim Rng As Range
Dim Val1 as Double

Set Rng = Worksheets("Sheet1").Range("D4")
Set Val1 = Rng.Value - 2

You only use Set with objects, and Val1 is not an object - try this.

Dim Rng As Range
Dim Val1 as Double

    Set Rng = Worksheets("Sheet1").Range("D4")
    Val1 = Rng.Value - 2

Or if you want to subtract 2 days from the date in D4 try this.

Dim Rng As Range

    Set Rng = Worksheets("Sheet1").Range("D4")
    Rng.Value = Rng.Value - 2

 

[Joe4]

eh, doesn't this code give a similar error to the original code.

Ironic! Talk about overlooking the obvious!
I forgot to remove the "Set" command in the value calculation.

Good catch, Norie. I will blame that on Monday!