Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Add Formula to Cell With VBA Macro Code

  1. #1
    Join Date
    2nd March 2006
    Posts
    98
    Usergroup
    Registered Users

    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. #2
    Join Date
    31st January 2003
    Location
    Portsmouth, UK
    Posts
    748
    Usergroup
    Registered Users

    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)" 
    
    
    D

    Better a bad day on the water than a good day in the office

  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,904
    Usergroup
    Administrators

    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. #4
    Join Date
    2nd March 2006
    Posts
    98
    Usergroup
    Registered Users

    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

    Any advice please?

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    31st January 2003
    Location
    Portsmouth, UK
    Posts
    748
    Usergroup
    Registered Users

    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
    D

    Better a bad day on the water than a good day in the office

  6. #6
    Join Date
    2nd March 2006
    Posts
    98
    Usergroup
    Registered Users

    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. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,904
    Usergroup
    Administrators

    Re: Add Formula to Cell With VBA Macro Code

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

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 6
    Last Post: May 22nd, 2008, 01:33
  2. Replies: 8
    Last Post: April 30th, 2008, 01:15
  3. Replies: 3
    Last Post: April 18th, 2008, 13:08
  4. Replies: 2
    Last Post: April 17th, 2008, 11:27
  5. Add Cell OFFSET Formula Via Macro Code
    By Bang25 in forum Excel General
    Replies: 4
    Last Post: February 23rd, 2008, 04:15

Bookmarks

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