Add Formula to Cell With VBA Macro Code

[newvbie]

Hello there. Can anyone please tell me what's wrong with the following vba code?

Range("B16").Formula = "=DATE(YEAR(" & Cells(16,1) & ",MONTH(" & Cells(16,1) +1 & ",0)"" 
 
 
where Cells(16,1) = "Feb-2007" 

Cheers.

[DaveR (UK)]

think you're missing a bracket....try

Range("B16").Formula = "=DATE(YEAR(" & Cells(16,1) & "),MONTH(" & Cells(16,1) +1 & "),0)" 

[Dave Hawley]

The best way is to Record a Macro Entering the needed formula.

[newvbie]

Hi there. I tried the following modified code:

Range("B16").Formula = "=DATE(YEAR(" & Cells(16, 1) & "),MONTH(" & Cells(16, 1) + 1 & "),0)" 
 'where cells(16,1).value="Feb-2007"

but the formula in the cell turned out to be:
=DATE(YEAR(1/2/2007),MONTH(2/2/2007),0)
and the result : 00/01/1900

Any advice please?

[DaveR (UK)]

Not sure what you mean - the formula does what you'd asked for...

however...if you are trying to get a reference to the cell in the formula...try

Range("B16").Formula = "=DATE(YEAR(R16C1),MONTH(R16C1),0)" 

If you are using R1C1 cell reference or

Range("B16").Formula = "=DATE(YEAR($A$16),MONTH($A$16),0)" 

if not

[newvbie]

Sorry. I've got a little muddle myself! The required formula should be created in range("B16) and the cell(r,c) should be the cell "A16" where Feb-2007 was stored.

Sorry for that!

[Dave Hawley]

[QUOTE]The best way is to Record a Macro Entering the needed formula.QUOTE]