Display contents of variable in a cell

[ryanmorris]

Hello all,

I am currently working on a sheet that prompts the user to enter yesterday's date in an input box which then stores this entry as a variable (previousDate).

On a tab in this sheet, I have a column for product ID (Column B), report date (Column H) and a tab for Origination date (Column J).

In Column J I have formulas starting in J2 that check another file using a vlookup. Basically it checks to see if the product IDs located in Column B are found in another workbook. If found, it returns the origination date associated with that product ID in Column J. If it is not found, I would like to enter the date that the user entered in the input box, stored in variable previousDate.

In other words,

=IF(ISNA(VLOOKUP(B2, otherworkbookB:M, 5, 0),previousDate,VLOOKUP(B2,otherworkbookB:M, 5, 0))

How would I pass the contents of the variable 'previousDate' into a formula in VBA?

Thanks in advance guys, I really appreciate the help!

[Dom83]

Hi,

Please Login or Register  to view this content.

Cheers,
Dom

[ryanmorris]

=IF(ISNA(VLOOKUP(B2, otherworkbookB:M, 5, 0),previousDate,VLOOKUP(B2,otherworkbookB:M, 5, 0))

I'm not sure how to work your code into the above formula

[Dom83]

Hi,

Maybe I misunderstood what you're asking. The first line of code prompts the user for a value and stores this in your variable previousDate. You can then use this variable in a formula as you have done in your post (perhaps using DateValue(previousDate) to ensure it's converted from a string to a date. What code are you using at the moment to show the inputbox?

Cheers,
Dom

[ryanmorris]

Ah yea I should have included a test file first. Sorry about that!

I've attached an example of what I'm doing. If you run the Macro right now and put in a date, try 8/12/2011 for instance, you'll see that some of the fields return a 1/0/1900 date.

If you go into the code, and comment out the IF(ISNA(VLOOKUP that's running and then uncomment out the IF(ISNA(VLOOKUP that isn't running, then re-run it, it'll return the date I'm looking for by referring to the DATE labeled cell.

[ryanmorris]

Any ideas anyone?

[jaslake]

Hi ryanmorris

Try changing these two lines of code

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Let me know if that helps.