Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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

    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

    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,717

    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 18:38.

  4. #4
    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

    Any advice please?

    Excel Video Tutorials / Excel Dashboards Reports


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

    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

    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,717

    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, 02:33
  2. Replies: 8
    Last Post: April 30th, 2008, 02:15
  3. Replies: 3
    Last Post: April 18th, 2008, 14:08
  4. Replies: 2
    Last Post: April 17th, 2008, 12:27
  5. Add Cell OFFSET Formula Via Macro Code
    By Bang25 in forum EXCEL HELP
    Replies: 4
    Last Post: February 23rd, 2008, 05: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