Solved

Excel Macro calling VB code ActiveCell.Formula issue with date

Posted on 2013-11-13
2
343 Views
Last Modified: 2013-11-14
I have a hotkey setup to call a Macro that is VB code.

I get the input box and after entering a date the cells update with #name value instead of doing the date calculation.

I've tried this:

    Dim agedate As String
    agedate = InputBox("enter age from date mm/dd/yyyy", "Data Entry")
    result = CDate(agedate)
    Dim adate As Date
    adate = DateValue(agedate)
    ActiveCell.FormulaR1C1 = "=DAYS360([@[Disch Date]]," & adate & ")"


And this:

    Dim AgeDate As Date
    AgeDate = InputBox("Enter date of ATB mm/dd/yyyy", "Data Entry")
    ActiveCell.FormulaR1C1 = "=DAYS360([@[Disch Date]],AgeDate)"


Any help would be appreciated.

Regards,
Torrey
0
Comment
Question by:Torrey Bates
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39647041
Torrey,
Assuming that you are using m/d/yyyy short date format, you can use:
    Dim agedate As String
    agedate = InputBox("enter age from date mm/dd/yyyy", "Data Entry")
    ActiveCell.FormulaR1C1 = "=DAYS360([@[Disch Date]],""" & agedate & """)"

Open in new window

Brad
0
 
LVL 2

Author Closing Comment

by:Torrey Bates
ID: 39648977
Thanks!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question