Solved

Excel Macro calling VB code ActiveCell.Formula issue with date

Posted on 2013-11-13
2
306 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:elitesys
2 Comments
 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
Comment Utility
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:elitesys
Comment Utility
Thanks!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now