Recall an InputBox value to be used in a formula

[Bowlzy101]

Hi All,
I am constructing some code that initially begins by asking for a date using an inputbox.
Then later in the macro I am trying to use the date submitted to calculate number of days in a formula. The lines that matter are as follows:

Dim MonthEndDate As Date
MonthEndDate = InputBox("What is the date of the month end? dd/mm/yyyy")

Range("E2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<=MonthEndDate,"""",""Y"")"

where the RC[-1] is a date in E2 and the MonthEndDate is the date input into the InputBox.

The formula does not work and comes up as #NAME?

Any solutions? Forever greatful

Ian

 

[JackDanIce]

Try:

Dim MonthEndDate As Date
MonthEndDate = InputBox("What is the date of the month end? dd/mm/yyyy")
Range("E2").FormulaR1C1 = "=IF(RC[-1]<=" & MonthEndDate & ","""",""Y"")"

You're trying to enter your variable MonthEndData into a formula within the worksheet cell and the sheet doesn't recognise the variable MonthEndDate, so you need to seperate it out of the formula

 

[Bowlzy101]

Thats perfect - thank you Jack

 

[Bowlzy101]

Hello again,
I have just noticed that the formula does now work because the MonthEndDate is not in speech marks on the data. E2 is formatted as a Date and I have declared the Date variable in the macro.
The formula turns out like this:
=IF(D2<=28/02/2011,"","Y") <<when I type in 28/02/11 as the date in the input box
but should look like this (apparently):
=IF(D2<="28/02/2011","","Y")

If I put speech marks round the coding in the macro it does not work and the formula turns out like this:
=IF(D2<=MonthEndDate,"","Y")

Any solutions?

Thanks for helping

 

[Bowlzy101]

Hello,
Just wondering if somebody is able to help me...
I thought this formula worked but it doesnt:

Dim MonthEndDate As Date
MonthEndDate = InputBox("What is the date of the month end? dd/mm/yyyy")
Range("E2").FormulaR1C1 = "=IF(RC[-1]<=" & MonthEndDate & ","""",""Y"")"

D2 shows a date and I use an IF formula to ask whether the date is less than the month end date. If it is show [blank] if it isnt show "Y".

After I have filled the formula down through column E every entry shows a Y but there are definitely entries which should be blank. If I manually change the formula to put speech marks round the inputted date and fill down every entry changes to a blank but still does not work. I have declared the MonthEndDate as a Date and column D is formatted as a date.

Is anyone able to advise?

Thanks

Ian

 

[JackDanIce]

Try either:

Dim MonthEndDate As String
MonthEndDate = InputBox("What is the date of the month end? dd/mm/yyyy")
Range("E2").FormulaR1C1 = "=IF(RC[-1]<=" & MonthEndDate & ","""",""Y"")"

Or

Dim MonthEndDate As Date
MonthEndDate = InputBox("What is the date of the month end? dd/mm/yyyy")
Range("E2").FormulaR1C1 = "=IF(RC[-1]<=""" & MonthEndDate & ""","""",""Y"")"

Though I'm using guess work as I don't know the setup of your spreadsheet

 

[Bowlzy101]

Hello Jack,
I had tried a few variations on this but it fails to work. I have tried your variations but neither of them give the correct response although it is very close.

I have a suggestion but I am unsure how to put it together:

Using this from above:

Dim MonthEndDate As Date
MonthEndDate = InputBox("What is the date of the month end? dd/mm/yyyy")
Range("E2").FormulaR1C1 = "=IF(RC[-1]<=" & MonthEndDate & ","""",""Y"")"

Is there anyway I can use the =DATE(YYYY,MM,DD) formula within the If formula but rather than typing in the YYYY,MM,DD get the formula to pick up the MonthEndDate figure accurately? I have tried to do this using speech marks, & ampesands and brackets but i must be doing something wrong

Kind regards

Ian