vba date formulas

[bryanrobson]

I have some date formulas in 3 cells which work but I want to put them into VBA code. My VBA is limited and I'm trying to pick up things. It doesnt like what I have done

=DATE(YEAR(P5),MONTH(P5)*1,1)
=DATE(YEAR(P5), MONTH(P5)-12, 1)
=DAY(EOMONTH($P$5,0))

VBA code
Range("R5").Value = DateSerial(Year(P5), Month(P5) * 1, 1)
Range("S5").Value = DateSerial(Year(P5), Month(P5) - 12, 1)
Range("U5").Value = Day(DateSerial(Year(P5), Month(P5) + 1, 1) - 1)

P5 has the date in it format dd/mm/yyyy

Thanks in advance

 

[RoryA]

You need to refer to Range("P5") not just P5. You'd be best to store that in a variable really. Why are you multiplying the month by 1? What does that achieve?

 

[Joe4]

You just need to change how you are referencing the cells, i.e.
instead of:

Range("S5").Value = DateSerial(Year(P5), Month(P5) - 12, 1)

it should look like:

Range("S5").Value = DateSerial(Year(Range("P5").Value), Month(Range("P5").Value) - 12, 1)

Also, in your first formula, I don't think it is necessary to multiply the MONTH result by 1. Month already returns a number, so no need to try to convert/coerce it to a number.

 

[bryanrobson]

You just need to change how you are referencing the cells, i.e.
instead of:

Range("S5").Value = DateSerial(Year(P5), Month(P5) - 12, 1)

it should look like:

Range("S5").Value = DateSerial(Year(Range("P5").Value), Month(Range("P5").Value) - 12, 1)

Also, in your first formula, I don't think it is necessary to multiply the MONTH result by 1. Month already returns a number, so no need to try to convert/coerce it to a number.

Hi Joe. Thanks for your reply. I did try putting the " around the P5 but I didn't realise I needed the range and value adding too. That worked a treat. thanks very much.

 

[DanteAmor]

I show you other alternatives:

'It's the formula, but inside the Evaluate function

Sub test_1()
  Range("S5").Value = Evaluate("=DATE(YEAR(P5), MONTH(P5)-12, 1)")
  Range("U5").Value = Evaluate("=DAY(EOMONTH($P$5,0))")
End Sub

'It's the formula, but inside the Evaluate method, use brackets instead of the evaluate function

Sub test_1a()
  Range("S5").Value = ["=DATE(YEAR(P5), MONTH(P5)-12, 1)"]
  Range("U5").Value = ["=DAY(EOMONTH($P$5,0))"]
End Sub

'They are the VBA functions but the cell is enclosed in square brackets
'(this means that it is evaluating a cell) like range("P5").value

Sub test_2()
  Range("S5").Value = DateSerial(Year([P5]), Month([P5]) - 12, 1)
  Range("U5").Value = Day(DateSerial(Year([P5]), Month([P5]) + 1, 1) - 1)
End Sub

 

[Joe4]

Hi Joe. Thanks for your reply. I did try putting the " around the P5 but I didn't realise I needed the range and value adding too. That worked a treat. thanks very much.

If you put the " around the P5 but don't use "Range", it will just be treated as a literal text value.
The way you originally had it, P5 is treated as a variable, but it isn't set to anything.
The general rule of thumb is anything between double-quotes in VBA is treated as a literal text value, and things outside of them are functionv, variables, etc.

BTW, the ".Value" isn't really needed in this case. If you leave it off, it is implied. But it doesn't hurt.

 

[bryanrobson]

I show you other alternatives:

'It's the formula, but inside the Evaluate function

Sub test_1()
  Range("S5").Value = Evaluate("=DATE(YEAR(P5), MONTH(P5)-12, 1)")
  Range("U5").Value = Evaluate("=DAY(EOMONTH($P$5,0))")
End Sub

'It's the formula, but inside the Evaluate method, use brackets instead of the evaluate function

Sub test_1a()
  Range("S5").Value = ["=DATE(YEAR(P5), MONTH(P5)-12, 1)"]
  Range("U5").Value = ["=DAY(EOMONTH($P$5,0))"]
End Sub

'They are the VBA functions but the cell is enclosed in square brackets
'(this means that it is evaluating a cell) like range("P5").value

Sub test_2()
  Range("S5").Value = DateSerial(Year([P5]), Month([P5]) - 12, 1)
  Range("U5").Value = Day(DateSerial(Year([P5]), Month([P5]) + 1, 1) - 1)
End Sub

Thats interesting different ways of doing it. thanks very much