Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. Member
Join Date
2nd March 2006
Posts
98

## Add Formula to Cell With VBA Macro Code

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

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

where Cells(16,1) = "Feb-2007"

```
Cheers.

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Create A Formula To A Cell By Vba

think you're missing a bracket....try
VB:
```Range("B16").Formula = "=DATE(YEAR(" & Cells(16,1) & "),MONTH(" & Cells(16,1) +1 & "),0)"

```

3. ## Re: Create A Formula To A Cell By Vba

The best way is to Record a Macro Entering the needed formula.
Last edited by Dave Hawley; October 4th, 2006 at 17:38.

4. Member
Join Date
2nd March 2006
Posts
98

## Re: Add Formula to Cell With VBA Macro Code

Hi there. I tried the following modified code:

VB:
```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

Excel Video Tutorials / Excel Dashboards Reports

5. ## Re: Add Formula to Cell With VBA Macro Code

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
VB:
```Range("B16").Formula = "=DATE(YEAR(R16C1),MONTH(R16C1),0)"

```
If you are using R1C1 cell reference or
VB:
```Range("B16").Formula = "=DATE(YEAR(\$A\$16),MONTH(\$A\$16),0)"

```
if not

6. Member
Join Date
2nd March 2006
Posts
98

## Re: Add Formula to Cell With VBA Macro Code

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!

Excel Video Tutorials / Excel Dashboards Reports

7. ## Re: Add Formula to Cell With VBA Macro Code

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

There are currently 7 users browsing this thread. (0 members and 7 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno