Solved

Excel Macro calling VB code ActiveCell.Formula issue with date

Posted on 2013-11-13
2
331 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 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:elitesys
ID: 39648977
Thanks!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

839 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