F = Date(YEAR(F)+1,MONTH(F),DAY(F))) will not work

[mwheads]

Hi Everyone

I cannot get Date(YEAR(F)+1,MONTH(F),DAY(F))) to work in VBA but it works fine as a formula on a sheet.

I am trying to use it in VBA:

Sub RollOver()
If Range("E1") = 12 Then

Dim F As Object

For Each F In Range("B1:B12")

F = Date(YEAR(F)+1,MONTH(F),DAY(F)))

Next F

End If
End Sub


but I always get an error....Compile Error, Expected

as far as I can see there are no "(" missing and no matter how many I add or take away, it is not working for me.

does anybody know where the problem is.

Regards

 

[TonyJollans]

Hi mwheads,

Date used like that is a WORKSHEET Function. The equivalent VBA Function is DateSerial. To make it update cells, I think you'll also need to add the Value (as below).

[blue]F[b].Value[/b] = Date[b]Serial[/b](YEAR(F)+1,MONTH(F),DAY(F)))[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[mwheads]

Thanks Tony, that works great, easy when you know how!

Kind Regards,
Paul